Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2005
    Posts
    20

    Unanswered: A simple group by query seems Impossible

    I've been fighting with this all day there has to be an easy way to do this in a single query:

    FOR EACH fac_id, get the INSPECTION_ID of the earliest start_date:

    INSPECTION_ID FAC_ID START_DATE
    300720040628004 566 2004-07-07
    300720030618002 566 2003-06-25
    200820021219011 1436 2002-12-19
    300720030206002 1458 2003-02-03
    300720030206003 1458 2003-02-05
    300720030121002 1480 2003-03-25
    300720030121003 1480 2005-02-02
    200820031230001 1436 2003-12-30
    300720040616006 1566 2004-08-26
    300720040616001 1566 2001-08-26
    300720040616005 1566 2002-08-26
    300720040616002 1566 2003-08-26
    300720040616003 1566 2004-08-26
    300720040616004 1566 2006-08-26
    300720040504004 1569 2004-09-10

    DESIRED OUTPUT
    INSPECTION_ID FAC_ID START_DATE
    300720030618002 566 2003-06-25
    200820021219011 1436 2002-12-19
    300720030206002 1458 2003-02-03
    300720030121002 1480 2003-03-25
    200820031230001 1436 2003-12-30
    300720040616001 1566 2001-08-26
    300720040504004 1569 2004-09-10

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    you have FAC_ID 1436 listed twice with two start dates ... this appears to voilate the ground rules, since there are only 6 distinct FAC_ID values.

    Given that, this should work:

    Code:
     
    --  create the table
    create  table #temp (
    INSPECTION_ID bigint, 
    FAC_ID int,
    START_DATE datetime)
     
    --  populate the table
    insert into #temp
    select 300720040628004, 566 , '2004-07-07'  
    UNION
    select 300720030618002, 566 , '2003-06-25'  
    UNION
    select 200820021219011, 1436, '2002-12-19'  
    UNION
    select 300720030206002, 1458, '2003-02-03'  
    UNION
    select 300720030206003, 1458, '2003-02-05'  
    UNION
    select 300720030121002, 1480, '2003-03-25'  
    UNION
    select 300720030121003, 1480, '2005-02-02'  
    UNION
    select 200820031230001, 1436, '2003-12-30'  
    UNION
    select 300720040616006, 1566, '2004-08-26'  
    UNION
    select 300720040616001, 1566, '2001-08-26'  
    UNION
    select 300720040616005, 1566, '2002-08-26'  
    UNION
    select 300720040616002, 1566, '2003-08-26'  
    UNION
    select 300720040616003, 1566, '2004-08-26'  
    UNION
    select 300720040616004, 1566, '2006-08-26'  
    UNION
    select 300720040504004, 1569, '2004-09-10' 
     
    --  code to select FAC_ID with earliest start date
    select t.INSPECTION_ID, t.FAC_ID, t.START_DATE
    from #temp t
       inner join 
    (select t2.fac_id, min(t2.start_date) start_date
     from #temp t2
     group by t2.FAC_ID) t2 on t2.start_date = t.start_date
    order by t.fac_id
     
    --  only 6 distinct FAC_ID
    select distinct (fac_id)
    from #temp
     
    drop table #temp

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Jul 2002
    Posts
    58
    I'm simply going to assume that listing TWO 1436 rows in the output was a mistake, if not you're going to have to give a better explaination of what you want - if you want the earliest START_DATE for each FAC_ID, then shouldn't be getting two rows for a single FAC_ID, should you?

    Seems to me the problem is how to reterive the INSPECTION_ID from the minimum row without messing up the grouping. I've long wished SQL had the ability to do this...

    SELECT ... FROM .... WHERE MIN_OF(column)

    but alas, it does not exist natively. Here's a way to do it with a nested query.

    Code:
    SELECT i.*
       FROM inspection_table AS i
       JOIN (SELECT f=fac_id, s=MIN(start_date)
                FROM inspection_table
                GROUP BY fac_id) AS q ON i.fac_id = q.f AND i.start_date = q.s

Posting Permissions

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