Re: simple query question: return latest
От | Vincent Hikida |
---|---|
Тема | Re: simple query question: return latest |
Дата | |
Msg-id | 011101c4c85c$3df613a0$6401a8c0@HOMEOFFICE обсуждение исходный текст |
Ответ на | simple query question: return latest (Scott Frankel <leknarf@pacbell.net>) |
Список | pgsql-general |
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) > >
В списке pgsql-general по дате отправления: