pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedhelp with limiting query results
2+ messages / 2 participants
[nested] [flat]
* help with limiting query results
@ 2004-01-13 19:34 OizOne <rautaon@nic.fi>
0 siblings, 1 reply; 2+ messages in thread
From: OizOne @ 2004-01-13 19:34 UTC (permalink / raw)
To: pgsql-sql
Hi,
I have the following table in postgres:
hostname | username | logontime
----------+---------------+----------------------------
ws1 | rautaonn | 2004-01-13 21:25:01.100336
ws1 | administrator | 2004-01-13 21:25:07.706546
ws1 | testuser | 2004-01-13 21:25:16.084844
ws2 | testuser | 2004-01-13 21:25:18.683653
ws2 | testuser2 | 2004-01-13 21:25:20.862199
ws2 | administrator | 2004-01-13 21:25:25.932736
ws2 | oizone | 2004-01-13 21:25:30.107574
and I would need to create a query that selects each hostname only once with
username that has the latest timestamp in the logontime column.
The real table has about 5000 rows with ยจ500 different hostnames, and I
would need this query for reporting. Any help would be appreciated.
Thank in advance.
-Onni Rautanen
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: help with limiting query results
@ 2004-01-19 04:54 Tom Lane <tgl@sss.pgh.pa.us>
parent: OizOne <rautaon@nic.fi>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2004-01-19 04:54 UTC (permalink / raw)
To: OizOne <rautaon@nic.fi>; +Cc: pgsql-sql
"OizOne" <rautaon@nic.fi> writes:
> I would need to create a query that selects each hostname only once with
> username that has the latest timestamp in the logontime column.
SELECT DISTINCT ON is a convenient way to do this. See the "weather
reports" example in the SELECT reference page for a similar case.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-01-19 04:54 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-13 19:34 help with limiting query results OizOne <rautaon@nic.fi>
2004-01-19 04:54 ` Tom Lane <tgl@sss.pgh.pa.us>
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