Thread: simple query question: return latest
Still too new to SQL to have run across this yet ... How does one return the latest row from a table, given multiple entries of varying data? i.e.: given a table that looks like this: color | date --------+------------ red | 2004-01-19 blue | 2004-05-24 red | 2004-04-12 blue | 2004-05-24 How do I select the most recent entry for 'red'? Thanks in advance! Scott
Scott, On Nov 12, 2004, at 10:00 AM, Scott Frankel wrote: > color | date > --------+------------ > red | 2004-01-19 > blue | 2004-05-24 > red | 2004-04-12 > blue | 2004-05-24 > > > How do I select the most recent entry for 'red'? > SELECT color, MAX(date) FROM giventable WHERE color = 'red' -- omit this line if you'd like to see the latest date for each color GROUP BY color; OT hint: You might want to take a look at the list of PostgreSQL Keywords in the documentation and avoid using them (such as date) to help you avoid naming issues in the future. Hope this helps. Michael Glaesemann grzm myrealbox com
I interpreted the question slightly differently. I understood it to mean the
most recent instance of red which doesn't make much sense in this case but
let's say the table was
color | date | entered_by
--------+-----------------+---------------
red | 2004-01-19 | John
red | 2004-04-12 | Jane
and you wanted to pick up the row which Jane entered, then the statement
would be
SELECT g.color, g.date, g.entered_by
FROM giventable g
WHERE g.color = 'red'
AND g.date =
(SELECT MAX(g2.date)
FROM giventable g2
WHERE g2.color = g.color
)
or perhaps
SELECT g.color, g.date, g.entered_by
FROM giventable g
WHERE (g.color,g.date) =
(SELECT g2.color, MAX(g2.date)
FROM giventable g2
WHERE g2.color = 'red'
GROUP BY g2.color
)
etc. etc.
----- Original Message -----
From: "Michael Glaesemann" <grzm@myrealbox.com>
To: "Scott Frankel" <leknarf@pacbell.net>
Cc: <pgsql-general@postgresql.org>
Sent: Thursday, November 11, 2004 5:09 PM
Subject: Re: [GENERAL] simple query question: return latest
> Scott,
>
> On Nov 12, 2004, at 10:00 AM, Scott Frankel wrote:
>
> > color | date
> > --------+------------
> > red | 2004-01-19
> > blue | 2004-05-24
> > red | 2004-04-12
> > blue | 2004-05-24
> >
> >
> > How do I select the most recent entry for 'red'?
> >
>
> SELECT color, MAX(date)
> FROM giventable
> WHERE color = 'red' -- omit this line if you'd like to see the latest
> date for each color
> GROUP BY color;
>
> OT hint: You might want to take a look at the list of PostgreSQL
> Keywords in the documentation and avoid using them (such as date) to
> help you avoid naming issues in the future.
>
> Hope this helps.
>
> Michael Glaesemann
> grzm myrealbox com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
On Nov 11, 2004, at 5:09 PM, Michael Glaesemann wrote: > Scott, > > On Nov 12, 2004, at 10:00 AM, Scott Frankel wrote: > >> color | date >> --------+------------ >> red | 2004-01-19 >> blue | 2004-05-24 >> red | 2004-04-12 >> blue | 2004-05-24 >> >> >> How do I select the most recent entry for 'red'? >> > > SELECT color, MAX(date) > FROM giventable > WHERE color = 'red' -- omit this line if you'd like to see the latest > date for each color > GROUP BY color; Unless I'm missing something, this returns every listing for color=red, in max order. So if I want the ONE most recent entry, is this something I have to offload to my app that parses the returned rows? Or is there a function in postgres that can return THE most recent entry? > > OT hint: You might want to take a look at the list of PostgreSQL > Keywords in the documentation and avoid using them (such as date) to > help you avoid naming issues in the future. Hmm. Good tip. Bad example terminology. Thanks! Scott > > Hope this helps. > > Michael Glaesemann > grzm myrealbox com > > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
On Thu, 11 Nov 2004, Scott Frankel wrote: > > On Nov 11, 2004, at 5:09 PM, Michael Glaesemann wrote: > > > Scott, > > > > On Nov 12, 2004, at 10:00 AM, Scott Frankel wrote: > > > >> color | date > >> --------+------------ > >> red | 2004-01-19 > >> blue | 2004-05-24 > >> red | 2004-04-12 > >> blue | 2004-05-24 > >> > >> > >> How do I select the most recent entry for 'red'? > >> > > > > SELECT color, MAX(date) > > FROM giventable > > WHERE color = 'red' -- omit this line if you'd like to see the latest > > date for each color > > GROUP BY color; > > Unless I'm missing something, this returns every listing for color=red, > in max order. No. This returns one row having the maximum date. The GROUP BY means that you would get one row per color, but the where clause basically means there is only the one. However, it does not extend to getting other attributes of that row. You can do something like the subselect already mentioned in another mail, or use if you can use a PostgreSQL extension, you might want to look into DISTINCT ON which will often be faster. Something like: select DISTINCT ON (color) * from giventable where color='red' order by color, date desc; should give the attributes of the one row with color='red' having the highest date. With a bit of work (reverse opclasses), one can make an index which can be used to provide the filtering and ordering for such queries.
On Thu, Nov 11, 2004 at 05:00:46PM -0800, Scott Frankel wrote: > How does one return the latest row from a table, given multiple entries > of varying data? > i.e.: given a table that looks like this: > > color | date > --------+------------ > red | 2004-01-19 > blue | 2004-05-24 > red | 2004-04-12 > blue | 2004-05-24 > > How do I select the most recent entry for 'red'? One way would be to sort by date and use a LIMIT clause: SELECT * FROM colortable WHERE color = 'red' ORDER BY date DESC LIMIT 1; If you want the most recent entry for all colors then you could use SELECT DISTINCT ON: SELECT DISTINCT ON (color) * FROM colortable ORDER BY color, date DESC; In either case, if multiple records have the same date and the ORDER BY clause isn't specific enough to guarantee a certain order, then it's indeterminate which record you'll get. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
ORDER BY DESC LIMIT 1 is much simpler and more readable than a
sub-query.
Though the sub-query approach looks to be a good template for ensuring
more
accurate results by being more explicit.
Thanks to all who responded!
Scott
SELECT * FROM colortable WHERE color = 'red' ORDER BY date DESC LIMIT 1;
SELECT g.color, g.date, g.entered_by
FROM giventable g
WHERE g.color = 'red'
AND g.date =
(SELECT MAX(g2.date)
FROM giventable g2
WHERE g2.color = g.color
)
If 2 days are equal for color red, you still would get 2 rows returned.
Maybe the below is accurate.
SELECT g.color, g.date, g.entered_by
FROM giventable g
WHERE g.color = 'red'
AND g.date =
(SELECT MAX(g2.date)
FROM giventable g2
WHERE g2.color = g.color
) LIMIT 1
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Scott Frankel
> Sent: Thursday, November 11, 2004 11:13 PM
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] simple query question: return latest
>
>
>
> ORDER BY DESC LIMIT 1 is much simpler and more readable than
> a sub-query.
> Though the sub-query approach looks to be a good template for
> ensuring more accurate results by being more explicit.
>
> Thanks to all who responded!
> Scott
>
>
>
> SELECT * FROM colortable WHERE color = 'red' ORDER BY date
> DESC LIMIT 1;
>
>
>
> SELECT g.color, g.date, g.entered_by
> FROM giventable g
> WHERE g.color = 'red'
> AND g.date =
> (SELECT MAX(g2.date)
> FROM giventable g2
> WHERE g2.color = g.color
> )
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
>
Confidentiality Notice
The information contained in this e-mail is confidential and intended for use only by the person(s) or organization
listedin the address. If you have received this communication in error, please contact the sender at O'Neil &
Associates,Inc., immediately. Any copying, dissemination, or distribution of this communication, other than by the
intendedrecipient, is strictly prohibited.
[Top-posting fixed] On Fri, Nov 12, 2004 at 09:06:08AM -0500, Goutam Paruchuri wrote: > Scott Frankel wrote: > > > ORDER BY DESC LIMIT 1 is much simpler and more readable than a > > sub-query. Though the sub-query approach looks to be a good template > > for ensuring more accurate results by being more explicit. Scott, how would a subquery "ensure more accurate results by being more explicit"? > If 2 days are equal for color red, you still would get 2 rows returned. How do you figure, given the presence of LIMIT 1? -- Michael Fuhr http://www.fuhr.org/~mfuhr/
On Nov 12, 2004, at 8:24 AM, Michael Fuhr wrote: > [Top-posting fixed] > > On Fri, Nov 12, 2004 at 09:06:08AM -0500, Goutam Paruchuri wrote: > >> Scott Frankel wrote: >> >>> ORDER BY DESC LIMIT 1 is much simpler and more readable than a >>> sub-query. Though the sub-query approach looks to be a good template >>> for ensuring more accurate results by being more explicit. > > Scott, how would a subquery "ensure more accurate results by being > more explicit"? > Good question. I'm just now starting to construct sub-queries. Perhaps naively, I assumed that setting g.date explicitly equal to the results of a MAX function would return more reliable results than limiting a return list to just the first value listed. Though it's entirely possible that both approaches use the same logic under the hood. Nonetheless, I'm using the DESC LIMIT 1 approach for now as it yields the results I need and is much more readable. Thanks again! Scott
SELECT "date" FROM "table" WHERE "color" = 'red' ORDER BY "date" DESC LIMIT 1; Don't worry about names, just quote your identifiers. They will stand out and you can use anything you want. Jerry "Michael Glaesemann" <grzm@myrealbox.com> wrote in message news:89C8EF0C-3447-11D9-8FE6-000A95C88220@myrealbox.com... > Scott, > > On Nov 12, 2004, at 10:00 AM, Scott Frankel wrote: > >> color | date >> --------+------------ >> red | 2004-01-19 >> blue | 2004-05-24 >> red | 2004-04-12 >> blue | 2004-05-24 >> >> >> How do I select the most recent entry for 'red'? >> > > SELECT color, MAX(date) > FROM giventable > WHERE color = 'red' -- omit this line if you'd like to see the latest date > for each color > GROUP BY color; > > OT hint: You might want to take a look at the list of PostgreSQL Keywords > in the documentation and avoid using them (such as date) to help you avoid > naming issues in the future. > > Hope this helps. > > Michael Glaesemann > grzm myrealbox com > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >