Professional Web Applications Themes

Selecting 1 most recent item from 3 categories - MySQL

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...

  1. #1

    Default 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

    Adam Guest

  2. #2

    Default Re: Selecting 1 most recent item from 3 categories

    On Fri, 20 Jul 2007 18:25:10 +0200, Adam <com> wrote:
     

    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
    Rik Guest

  3. #3

    Default Re: Selecting 1 most recent item from 3 categories

    On Jul 20, 1:06 pm, Rik <com> wrote: 


    >
    > 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[/ref]

    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.

    Adam Guest

  4. #4

    Default Re: Selecting 1 most recent item from 3 categories

    On Jul 20, 1:06 pm, Rik <com> wrote: 


    >
    > 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[/ref]

    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)

    ZeldorBlat Guest

  5. #5

    Default Re: Selecting 1 most recent item from 3 categories

    Rik wrote: 
    >
    > 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?)[/ref]

    couldn't you also use his first one like this:
    select item, category, max(add_date) from table group by category limit 3;
    lark Guest

  6. #6

    Default Re: Selecting 1 most recent item from 3 categories

    On Jul 20, 3:04 pm, ZeldorBlat <com> wrote: [/ref]
    > [/ref]
    > [/ref]






    >
    > 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)[/ref]

    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.

    Adam Guest

  7. #7

    Default Re: Selecting 1 most recent item from 3 categories

    On Fri, 20 Jul 2007 21:40:26 +0200, Adam <com> wrote: 

    Ahum, one?
    Can you be absoutely 100% sure there is just 1 item with the maximum?
    --
    Rik Wasmus
    Rik Guest

  8. #8

    Default Re: Selecting 1 most recent item from 3 categories

    On Fri, 20 Jul 2007 21:33:19 +0200, lark <net> wrote:
     
    >>
    >> 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?)[/ref]
    >
    > couldn't you also use his first one like this:
    > select item, category, max(add_date) from table group by category limit
    > 3;[/ref]

    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
    Rik Guest

  9. #9

    Default Re: Selecting 1 most recent item from 3 categories

    On Jul 20, 3:59 pm, Rik <com> wrote: [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    >
    > 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[/ref]

    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 Guest

  10. #10

    Default Re: Selecting 1 most recent item from 3 categories


    "Adam" <com> wrote in message
    news:googlegroups.com... 

    Do a search on this forum for "Strawberry Query", that'll do exactly what
    you want


    Paul Guest

  11. #11

    Default Re: Selecting 1 most recent item from 3 categories

    On Jul 20, 4:27 pm, "Paul Lautman" <com>
    wrote: 




    >
    > Do a search on this forum for "Strawberry Query", that'll do exactly what
    > you want[/ref]

    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?

    Adam Guest

  12. #12

    Default Re: Selecting 1 most recent item from 3 categories

    Adam wrote: 
    >> 
    >> 
    >> 
    >> 
    >>
    >> Do a search on this forum for "Strawberry Query", that'll do exactly
    >> what you want[/ref]
    >
    > 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?[/ref]

    Somewhere in this forum, I remember posting a full explanation of how this
    query works, but I'll be ed 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-row.html
    (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.


    Paul Guest

  13. #13

    Default Re: Selecting 1 most recent item from 3 categories

    >I am totally amazed that Rik, Lark and ZeldorBlat didn't manage to point you at the
    answer.

    As am I.

    strawberry Guest

  14. #14

    Default Re: Selecting 1 most recent item from 3 categories

    On Sun, 22 Jul 2007 12:18:14 +0200, strawberry <com> wrote:
     
    > answer.
    >
    > As am I.
    >[/ref]

    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 :).

    --
    Rik Wasmus
    Rik Guest

  15. #15

    Default Re: Selecting 1 most recent item from 3 categories

    On Jul 22, 7:07 am, Rik <com> wrote: 
    > > answer.[/ref]

    >
    > 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 :).
    >
    > --
    > Rik Wasmus[/ref]

    Well, thanks all for your help.

    Adam

    Adam Guest

Similar Threads

  1. Selecting Item(s) on a DataGrid
    By Jaz Chana in forum Macromedia Flex General Discussion
    Replies: 3
    Last Post: May 9th, 07:51 PM
  2. Selecting item in listbox by data
    By FlashAsh99 in forum Macromedia Flash Actionscript
    Replies: 0
    Last Post: February 8th, 02:26 AM
  3. Replies: 2
    Last Post: February 5th, 12:11 AM
  4. selecting most recent record
    By Marc in forum Microsoft Access
    Replies: 3
    Last Post: September 8th, 11:13 AM
  5. Selecting an item in a drop down list
    By Bogdan in forum ASP.NET General
    Replies: 4
    Last Post: August 18th, 02:40 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139