Thread: simple query question: return latest

simple query question: return latest

From
Scott Frankel
Date:
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


Re: simple query question: return latest

From
Michael Glaesemann
Date:
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


Re: simple query question: return latest

From
"Vincent Hikida"
Date:
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)
>
>



Re: simple query question: return latest

From
Scott Frankel
Date:
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)
>


Re: simple query question: return latest

From
Stephan Szabo
Date:
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.

Re: simple query question: return latest

From
Michael Fuhr
Date:
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/

Re: simple query question: return latest

From
Scott Frankel
Date:
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
                 )



Re: simple query question: return latest

From
"Goutam Paruchuri"
Date:
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. 


Re: simple query question: return latest

From
Michael Fuhr
Date:
[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/

Re: simple query question: return latest

From
Scott Frankel
Date:
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


Re: simple query question: return latest

From
"Jerry III"
Date:
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)
>