pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp with simple query
5+ messages / 5 participants
[nested] [flat]
* Help with simple query
@ 2005-12-28 23:58 Collin Peters <cadiolis@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Collin Peters @ 2005-12-28 23:58 UTC (permalink / raw)
To: pgsql-sql
I have a simple table called notes which contains notes for users.
The table has 4 columns: note_id (auto-incrementing primary key),
user_id (foreign key to a users table), note (varchar), and
modified_date (timestamp).
Is there a nice simple query I can run that will return me a list of
all the *latest* notes for all users (users can have many notes in the
table)? I'm trying to figure out a simple way of doing it but seem to
be having some mental block or there is no easy way to do it.
The following query will return me all the latest dates, but I can't
return the note_id or subject with it.
SELECT n.user_id, max(n.modified_date)
FROM notes n
GROUP by n.user_id
ORDER BY n.user_id
Is this simpler than I am making it?
Regards,
Collin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Help with simple query
@ 2005-12-29 00:41 Frank Bax <fbax@sympatico.ca>
0 siblings, 0 replies; 5+ messages in thread
From: Frank Bax @ 2005-12-29 00:41 UTC (permalink / raw)
To: pgsql-sql
At 06:58 PM 12/28/05, Collin Peters wrote:
>The following query will return me all the latest dates, but I can't
>return the note_id or subject with it.
>SELECT n.user_id, max(n.modified_date)
>FROM notes n
>GROUP by n.user_id
>ORDER BY n.user_id
>
>Is this simpler than I am making it?
No, it's not "simple". You need to join the results of above sql back to
the original table (and relocate the order by clause):
SELECT notes.note_id, notes.user_id, maxx.max_date, notes.note
FROM
(SELECT n.user_id, max(n.modified_date) AS max_date FROM notes n GROUP by
n.user_id)
AS maxx
JOIN notes on notes.user_id = maxx.user_id AND notes.modified_date =
maxx.max_date
ORDER BY notes.user_id;
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Help with simple query
@ 2005-12-29 00:43 Tom Lane <tgl@sss.pgh.pa.us>
parent: Collin Peters <cadiolis@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2005-12-29 00:43 UTC (permalink / raw)
To: Collin Peters <cadiolis@gmail.com>; +Cc: pgsql-sql
Collin Peters <cadiolis@gmail.com> writes:
> Is there a nice simple query I can run that will return me a list of
> all the *latest* notes for all users (users can have many notes in the
> table)?
You can use SELECT DISTINCT ON for that, if you don't mind using a
Postgres-only feature. See the "weather reports" example in the SELECT
reference page.
If you want to stick to portable SQL, you can still do it, but it's
pretty ugly and slow. Look in the list archives for previous
discussions.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Help with simple query
@ 2005-12-29 01:31 PFC <lists@peufeu.com>
parent: Collin Peters <cadiolis@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: PFC @ 2005-12-29 01:31 UTC (permalink / raw)
To: Collin Peters <cadiolis@gmail.com>; pgsql-sql
If you want the latest by user, you can cheat a bit and use the fact that
the id's are incrementing, thus ordering by the id
is about the same as ordering by the date field. I know it can be inexact
in some corner cases, but it's a good approximation, and
very useful in practice :
SELECT user_id, max(note_id) FROM notes GROUP by user_id;
So :
SELECT * FROM notes WHERE id IN (SELECT max(note_id) FROM notes GROUP by
user_id) ;
Can postgres use the index on these max() clauses now ?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Help with simple query
@ 2005-12-29 01:47 George Pavlov <gpavlov@mynewplace.com>
0 siblings, 0 replies; 5+ messages in thread
From: George Pavlov @ 2005-12-29 01:47 UTC (permalink / raw)
To: Collin Peters <cadiolis@gmail.com>; pgsql-sql
or, from the "stupid tricks" category:
SELECT
n.user_id,
max(lpad(extract(epoch from n.modified_date),12,'0') || ' ' || note)
FROM notes n
GROUP by n.user_id
i am not *really* suggesting this!
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-12-29 01:47 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-28 23:58 Help with simple query Collin Peters <cadiolis@gmail.com>
2005-12-29 00:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-12-29 01:31 ` PFC <lists@peufeu.com>
2005-12-29 00:41 Re: Help with simple query Frank Bax <fbax@sympatico.ca>
2005-12-29 01:47 Re: Help with simple query George Pavlov <gpavlov@mynewplace.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox