 |
Newsgroups: comp.databases.mysql
From: Adam <sty...@gmail.com>
Date: Fri, 20 Jul 2007 09:25:10 -0700
Local: Fri 20 July 2007 17:25
Subject: 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: +----------+-------------+-------------------+-------------------+ There are 3 different categories, just entered into the table as Any help would be much appreciated, thanks. Adam You must Sign in before you can post messages.
To post a message, you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
| |||||||||||||
|
Newsgroups: comp.databases.mysql
From: Rik <luiheidsgoe...@hotmail.com>
Date: Fri, 20 Jul 2007 19:06:55 +0200
Local: Fri 20 July 2007 18:06
Subject: Re: Selecting 1 most recent item from 3 categories
On Fri, 20 Jul 2007 18:25:10 +0200, Adam <sty...@gmail.com> wrote: Do you want the ID or just the list? > 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 Without ID is the simplest: SELECT category, MAX(add_date) FROM table GROUP BY category With ID it's quite different: SELECT (anyone know a shorter one, seems a bit cludgy?) You must Sign in before you can post messages.
To post a message, you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
| |||||||||||||
|
Newsgroups: comp.databases.mysql
From: Adam <sty...@gmail.com>
Date: Fri, 20 Jul 2007 10:24:47 -0700
Local: Fri 20 July 2007 18:24
Subject: Re: Selecting 1 most recent item from 3 categories
On Jul 20, 1:06 pm, Rik <luiheidsgoe...@hotmail.com> wrote:
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. You must Sign in before you can post messages.
To post a message, you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
| |||||||||||||
|
Newsgroups: comp.databases.mysql
From: ZeldorBlat <zeldorb...@gmail.com>
Date: Fri, 20 Jul 2007 19:04:27 -0000
Local: Fri 20 July 2007 20:04
Subject: Re: Selecting 1 most recent item from 3 categories
On Jul 20, 1:06 pm, Rik <luiheidsgoe...@hotmail.com> wrote:
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, You must Sign in before you can post messages.
To post a message, you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
| |||||||||||||
|
Newsgroups: comp.databases.mysql
From: Rik <luiheidsgoe...@hotmail.com>
Date: Fri, 20 Jul 2007 21:56:31 +0200
Local: Fri 20 July 2007 20:56
Subject: Re: Selecting 1 most recent item from 3 categories
On Fri, 20 Jul 2007 21:40:26 +0200, Adam <sty...@gmail.com> wrote: Ahum, one? > 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. Can you be absoutely 100% sure there is just 1 item with the maximum? -- Rik Wasmus You must Sign in before you can post messages.
To post a message, you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
| |||||||||||||
|
Newsgroups: comp.databases.mysql
From: lark <ham...@sbcglobal.net>
Date: Fri, 20 Jul 2007 19:33:19 GMT
Local: Fri 20 July 2007 20:33
Subject: Re: Selecting 1 most recent item from 3 categories
couldn't you also use his first one like this:
select item, category, max(add_date) from table group by category limit 3; You must Sign in before you can post messages.
To post a message, you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
| |||||||||||||
|
Newsgroups: comp.databases.mysql
From: Rik <luiheidsgoe...@hotmail.com>
Date: Fri, 20 Jul 2007 21:59:19 +0200
Local: Fri 20 July 2007 20:59
Subject: Re: Selecting 1 most recent item from 3 categories
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 You must Sign in before you can post messages.
To post a message, you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
| |||||||||||||
|
Newsgroups: comp.databases.mysql
From: Adam <sty...@gmail.com>
Date: Fri, 20 Jul 2007 13:04:34 -0700
Local: Fri 20 July 2007 21:04
Subject: Re: Selecting 1 most recent item from 3 categories
On Jul 20, 3:59 pm, Rik <luiheidsgoe...@hotmail.com> wrote:
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 You must Sign in before you can post messages.
To post a message, you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
| |||||||||||||
|
Newsgroups: comp.databases.mysql
From: "Paul Lautman" <paul.laut...@btinternet.com>
Date: Fri, 20 Jul 2007 21:27:50 +0100
Local: Fri 20 July 2007 21:27
Subject: Re: Selecting 1 most recent item from 3 categories
news:1184948710.062291.55830@w3g2000hsg.googlegroups.com...
> Hi, I have a table from which I want to select the most recent entry Do a search on this forum for "Strawberry Query", that'll do exactly what > from each category. The table looks like this: > +----------+-------------+-------------------+-------------------+ > There are 3 different categories, just entered into the table as > Any help would be much appreciated, thanks. > Adam you want You must Sign in before you can post messages.
To post a message, you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
| |||||||||||||