Selecting 1 most recent item from 3 categories

Hi, I have a table from which I want to select the most recent entry
from each category. The table looks like this:

+----------+-------------+-------------------+-------------------+
| id | item | category | add_date |

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

On Fri, 20 Jul 2007 18:25:10 +0200, Adam <styles@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:
>
> +----------+-------------+-------------------+-------------------+
> | id | item | category | add_date |
>
> 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 Jul 20, 1:06 pm, Rik <luiheidsgoe...@hotmail.com> wrote:
> 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:
>
> > +----------+-------------+-------------------+-------------------+
> > | id | item | category | add_date |
>
> > 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 Jul 20, 1:06 pm, Rik <luiheidsgoe...@hotmail.com> wrote:
> 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:
>
> > +----------+-------------+-------------------+-------------------+
> > | id | item | category | add_date |
>
> > 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)

Rik wrote:
> On Fri, 20 Jul 2007 18:25:10 +0200, Adam <styles@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:
>>
>> +----------+-------------+-------------------+-------------------+
>> | id | item | category | add_date |
>>
>> 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 Jul 20, 3:04 pm, ZeldorBlat <zeldorb...@gmail.com> wrote:
> On Jul 20, 1:06 pm, Rik <luiheidsgoe...@hotmail.com> wrote:
>
>
>
> > 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:
>
> > > +----------+-------------+-------------------+-------------------+
> > > | id | item | category | add_date |
>
> > > 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)

These all seem to be close, but as of yet I can't get it to work.

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.

On Fri, 20 Jul 2007 21:40:26 +0200, Adam <styles@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

On Fri, 20 Jul 2007 21:33:19 +0200, lark <hamzee@sbcglobal.net> wrote:

> Rik wrote:
>> On Fri, 20 Jul 2007 18:25:10 +0200, Adam <styles@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:
>>>
>>> +----------+-------------+-------------------+-------------------+
>>> | id | item | category | add_date |
>>>
>>> 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

On Jul 20, 3:59 pm, Rik <luiheidsgoe...@hotmail.com> wrote:
> On Fri, 20 Jul 2007 21:33:19 +0200, lark <ham...@sbcglobal.net> wrote:
> > Rik wrote:
> >> 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:
>
> >>> +----------+-------------+-------------------+-------------------+
> >>> | id | item | category | add_date |
>
> >>> 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.

"Adam" <styles@gmail.com> wrote in message
news:1184948710.062291.55830@w3g2000hsg.googlegrou ps.com...
> Hi, I have a table from which I want to select the most recent entry
> from each category. The table looks like this:
>
> +----------+-------------+-------------------+-------------------+
> | id | item | category | add_date |
>
> 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