Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2009
    Posts
    2

    Unanswered: Postgres Query Help

    Hello all,

    It's been a while since I've done a lot of SQL work so I'm afraid I'm stuck with a query that should be pretty simple . I'm running Postgresql 8.3.3.

    I have a table that tracks the execution of a process ("a run"). The table is very simple containing only runid, created_at and updated_at fields. "Runs" can occur at any time and any date. What I'm trying to do is extract the first runid and run date (created_at) of each month for which there are records in the table.

    Here's some sample data:

    runid created_at
    1 2008-11-25 01:29:24.298964
    2 2008-11-25 01:32:42.650082
    3 2008-11-25 01:34:14.961813
    4 2008-11-25 21:29:09.491419
    5 2008-11-26 12:38:05.004476
    6 2008-11-26 23:24:48.909054
    :
    9 2008-12-01 00:50:30.669273
    10 2008-12-01 02:16:00.150272
    11 2008-12-02 00:56:57.268226
    :
    16 2008-12-15 22:00:49.123941
    17 2009-01-06 02:17:40.219409
    18 2009-01-07 01:53:26.933354
    19 2009-01-11 15:24:18.322905

    I'm trying to get runids 1, 9 and 17.

    I'd appreciate any help / suggestions you can offer.

    Thanks,
    Furgy

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT t.runid 
         , t.created_at
      FROM daTable AS t
    INNER
      JOIN ( SELECT MIN(created_at) AS min_date
               FROM daTable
             GROUP
                 BY DATE_TRUNC('month',created_at) 
           ) AS m
        ON m.min_date = t.created_at
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2009
    Posts
    2

    Nailed it!

    Perfect . Exactly what i was looking for.

    I've been pulling it apart to understand it a bit better. Guess I need to refresh myself on inner joins.

    Thanks much,
    Furgy

Posting Permissions

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