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

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

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

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

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

[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