On Fri, 20 Jul 2007 18:25:10 +0200, Adam <sty...@gmail.com> wrote: > Hi, I have a table from which I want to select the most recent entry > from each category. The table looks like this:
> There are 3 different categories, just entered into the table as > varchar(30). "add_date" is datetime.
Do you want the ID or just the list?
Without ID is the simplest:
SELECT category, MAX(add_date) FROM table GROUP BY category
With ID it's quite different:
SELECT x.id x.category x.add_date FROM table x JOIN (SELECT category, MAX(add_date) FROM table GROUP BY category) y ON y.category = x. category AND y.add_date = x. add_date
(anyone know a shorter one, seems a bit cludgy?) -- Rik Wasmus
> On Fri, 20 Jul 2007 18:25:10 +0200, Adam <sty...@gmail.com> wrote: > > Hi, I have a table from which I want to select the most recent entry > > from each category. The table looks like this:
> > There are 3 different categories, just entered into the table as > > varchar(30). "add_date" is datetime.
> Do you want the ID or just the list?
> Without ID is the simplest:
> SELECT category, MAX(add_date) FROM table GROUP BY category
> With ID it's quite different:
> SELECT > x.id > x.category > x.add_date > FROM table x > JOIN (SELECT category, MAX(add_date) FROM table GROUP BY category) y > ON y.category = x. category > AND y.add_date = x. add_date
> (anyone know a shorter one, seems a bit cludgy?) > -- > Rik Wasmus
Sorry, to clarify I actually want to pull the item names (defined by "item"). I will try to use your second example, just substituting where appropriate.
> On Fri, 20 Jul 2007 18:25:10 +0200, Adam <sty...@gmail.com> wrote: > > Hi, I have a table from which I want to select the most recent entry > > from each category. The table looks like this:
> > There are 3 different categories, just entered into the table as > > varchar(30). "add_date" is datetime.
> Do you want the ID or just the list?
> Without ID is the simplest:
> SELECT category, MAX(add_date) FROM table GROUP BY category
> With ID it's quite different:
> SELECT > x.id > x.category > x.add_date > FROM table x > JOIN (SELECT category, MAX(add_date) FROM table GROUP BY category) y > ON y.category = x. category > AND y.add_date = x. add_date
> (anyone know a shorter one, seems a bit cludgy?) > -- > Rik Wasmus
That probably is the best way. You could probably do something like this, but the performance probably won't be as good:
SELECT x.id, x.category, x.add_date FROM table x WHERE x.add_date = (SELECT MAX(y.add_date) FROM table y WHERE y.category = x.category)
>> There are 3 different categories, just entered into the table as >> varchar(30). "add_date" is datetime.
> Do you want the ID or just the list?
> Without ID is the simplest:
> SELECT category, MAX(add_date) FROM table GROUP BY category
> With ID it's quite different:
> SELECT > x.id > x.category > x.add_date > FROM table x > JOIN (SELECT category, MAX(add_date) FROM table GROUP BY category) y > ON y.category = x. category > AND y.add_date = x. add_date
> (anyone know a shorter one, seems a bit cludgy?)
couldn't you also use his first one like this: select item, category, max(add_date) from table group by category limit 3;
On Fri, 20 Jul 2007 21:40:26 +0200, Adam <sty...@gmail.com> wrote: > To summarize, I'm wanting to choose the most recent item (and I'll be > using the "item" column for my purposes) from each of the 3 > categories. So three items, one from each category.
Ahum, one? Can you be absoutely 100% sure there is just 1 item with the maximum? -- Rik Wasmus
>>> There are 3 different categories, just entered into the table as >>> varchar(30). "add_date" is datetime.
>> Do you want the ID or just the list?
>> Without ID is the simplest:
>> SELECT category, MAX(add_date) FROM table GROUP BY category
>> With ID it's quite different:
>> SELECT >> x.id >> x.category >> x.add_date >> FROM table x >> JOIN (SELECT category, MAX(add_date) FROM table GROUP BY category) y >> ON y.category = x. category >> AND y.add_date = x. add_date
>> (anyone know a shorter one, seems a bit cludgy?)
> couldn't you also use his first one like this: > select item, category, max(add_date) from table group by category limit > 3;
I think you're missing the point. He states he wants the most recent entry from _each_ category. There just _happen_ to be three. But if you only want three, by all means, use a limit. -- Rik Wasmus
> >>> There are 3 different categories, just entered into the table as > >>> varchar(30). "add_date" is datetime.
> >> Do you want the ID or just the list?
> >> Without ID is the simplest:
> >> SELECT category, MAX(add_date) FROM table GROUP BY category
> >> With ID it's quite different:
> >> SELECT > >> x.id > >> x.category > >> x.add_date > >> FROM table x > >> JOIN (SELECT category, MAX(add_date) FROM table GROUP BY category) y > >> ON y.category = x. category > >> AND y.add_date = x. add_date
> >> (anyone know a shorter one, seems a bit cludgy?)
> > couldn't you also use his first one like this: > > select item, category, max(add_date) from table group by category limit > > 3;
> I think you're missing the point. He states he wants the most recent entry > from _each_ category. There just _happen_ to be three. But if you only > want three, by all means, use a limit. > -- > Rik Wasmus
There will definitely only be three, as it's a very small and simple utility for someone to add items to be donated. There are 3 donation categories, so I'm trying to show the 1 most recently requested donation from each category.
At any rate, I think I, and others, have supplied the right method but there must be something wrong with my data, because it seems to just be picking one random one from each category. It's always the same 3, but they're not the most recent.
> > There are 3 different categories, just entered into the table as > > varchar(30). "add_date" is datetime.
> > Any help would be much appreciated, thanks.
> > Adam
> Do a search on this forum for "Strawberry Query", that'll do exactly what > you want
Hey, thanks - I got it working with your "Strawberry Query" but I must confess I have no idea how it works, or if it will always work. At any rate, thanks very much.
>>> There are 3 different categories, just entered into the table as >>> varchar(30). "add_date" is datetime.
>>> Any help would be much appreciated, thanks.
>>> Adam
>> Do a search on this forum for "Strawberry Query", that'll do exactly >> what you want
> Hey, thanks - I got it working with your "Strawberry Query" but I must > confess I have no idea how it works, or if it will always work. At any > rate, thanks very much.
> Why do you call it the Strawberry Query?
Somewhere in this forum, I remember posting a full explanation of how this query works, but I'll be blowed if I can find it now! But the following reference to the manual contains a very succinct explanation of how it works: http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-r... (See: "The LEFT JOIN works on the basis that ...")
As to why I call it the Strawberry Query: The question that you asked comes up just soooooo many times on this forum. It is so common that I am totally amazed that Rik, Lark and ZeldorBlat didn't manage to point you at the answer. Anyway, a while back when this sort of question was asked about 3 times in the same week, a poster by the handle of Strawberry, constantly posted this query format to answer the question.
On Sun, 22 Jul 2007 12:18:14 +0200, strawberry <zac.ca...@gmail.com> wrote: >> I am totally amazed that Rik, Lark and ZeldorBlat didn't manage to >> point you at the > answer.
> As am I.
Still not conditioned enough :-). Then again, I've never had the problem my database was the bottleneck, so there's probably a lot out there that still is easily optimisable. The moment I run into trouble I'll think 'Strawberry', I promise :).
On Jul 22, 7:07 am, Rik <luiheidsgoe...@hotmail.com> wrote:
> On Sun, 22 Jul 2007 12:18:14 +0200, strawberry <zac.ca...@gmail.com> wrote: > >> I am totally amazed that Rik, Lark and ZeldorBlat didn't manage to > >> point you at the > > answer.
> > As am I.
> Still not conditioned enough :-). Then again, I've never had the problem > my database was the bottleneck, so there's probably a lot out there that > still is easily optimisable. The moment I run into trouble I'll think > 'Strawberry', I promise :).
|
