Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Location
    Finland
    Posts
    1

    Unanswered: help with limiting query results

    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

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    select t.*
    from table t
    where logontime = (select max(logontime) from table where id = t.id);
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •