simple query question: return latest

[Scott Frankel]

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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

[Michael Glaesemann]

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 YourEmailAddres sHere" to ma*******@postg resql.org)

[Jerry III]

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" <gr**@myrealbox .com> wrote in message
news:89******** *************** ***********@myr ealbox.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 YourEmailAddres sHere" to ma*******@postg resql.org)

[Vincent Hikida]

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" <gr**@myrealbox .com>
To: "Scott Frankel" <le*****@pacbel l.net>
Cc: <pg***********@ 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 YourEmailAddres sHere" to ma*******@postg resql.org)

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Scott Frankel]

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 YourEmailAddres sHere" to ma*******@postg resql.org)

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Stephan Szabo]

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.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Michael Fuhr]

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/

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Scott Frankel]

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 ma*******@postg resql.org