Thread: help with limiting query results

Hi,

I have the following table in postgres:

hostname |   username    |         logontime
----------+---------------+----------------------------ws1      | rautaonn      | 2004-01-13 21:25:01.100336ws1      |
administrator| 2004-01-13 21:25:07.706546ws1      | testuser      | 2004-01-13 21:25:16.084844ws2      | testuser
|2004-01-13 21:25:18.683653ws2      | testuser2     | 2004-01-13 21:25:20.862199ws2      | administrator | 2004-01-13
21:25:25.932736ws2     | 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

"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