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 majordomo (AT) postgresql (DOT) org

Scott,

On Nov 12, 2004, at 10:00 AM, Scott Frankel wrote:

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 (AT) postgresql (DOT) org)

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 (AT) myrealbox (DOT) com> wrote

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 (AT) myrealbox (DOT) com>
To: "Scott Frankel" <leknarf (AT) pacbell (DOT) net>
Cc: <pgsql-general (AT) postgresql (DOT) org>
Sent: Thursday, November 11, 2004 5:09 PM
Subject: Re: [GENERAL] simple query question: return latest




---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

On Nov 11, 2004, at 5:09 PM, Michael Glaesemann wrote:

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?


Hmm. Good tip. Bad example terminology.

Thanks!
Scott





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

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

On Thu, 11 Nov 2004, Scott Frankel wrote:

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

On Thu, Nov 11, 2004 at 05:00:46PM -0800, Scott Frankel wrote:

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

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 (AT) postgresql (DOT) org

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



Confidentiality Notice
The information contained in this e-mail is confidential and intended for use only by the person(s) or organization listed in 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 intended recipient, is strictly prohibited.


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

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

[Top-posting fixed]

On Fri, Nov 12, 2004 at 09:06:08AM -0500, Goutam Paruchuri wrote:

Scott, how would a subquery "ensure more accurate results by being
more explicit"?

How do you figure, given the presence of LIMIT 1?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org