Re: simple query question: return latest - Mailing list pgsql-general

From Goutam Paruchuri
Subject Re: simple query question: return latest
Date
Msg-id B2C547DF42419645804F05B54290755ADC7CC4@DAYTONEX.oneilinc.net
Whole thread Raw
In response to simple query question: return latest  (Scott Frankel <leknarf@pacbell.net>)
Responses Re: simple query question: return latest
List pgsql-general
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. 


pgsql-general by date:

Previous
From: Matteo Beccati
Date:
Subject: Re: query with table alias
Next
From: Michelle Konzack
Date:
Subject: How many secondary databases can I create?