Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2004
    Posts
    3

    Unanswered: Help with SQL Query

    Hi all,

    I have a table as follows:
    ID1 ID2 Property1 Property2 Date
    1 23 P1 P2 01/04/2004
    1 24 P22 P3 02/04/2004
    2 25 P13 P22 01/05/2004
    2 26 P34 P76 31/01/2004

    What I want to retrieve is this:
    For each value of ID1, I want to retrieve ONE record from all the ones sharing the same ID1 value, and this record is the earlist record of these.

    For example, if I have the data above, I want to retrieve the following result

    1 23 P1 P2 01/04/2004
    2 26 P34 P76 31/01/2004

    Any ideas how to do this? I've tried various GROUP BY and JOINS but can't seem to get anywhere near

    Thanks!

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Help with SQL Query

    There are 2 steps involved:

    1) What is the earliest date per ID1?

    select id1, min(date) from table group by id1;

    2) Get the records where the id1 and date values are in the list generated by query 1:

    select * from table
    where (id1, date) in (select id1, min(date) from table group by id1);

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54

    Re: Help with SQL Query

    Originally posted by andrewst
    There are 2 steps involved:

    1) What is the earliest date per ID1?

    select id1, min(date) from table group by id1;

    2) Get the records where the id1 and date values are in the list generated by query 1:

    select * from table
    where (id1, date) in (select id1, min(date) from table group by id1);
    Just an observation, but this can produce more than one row per value of ID1 if there are "ties" with the lowest date value.

    -PatP

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    and whaddya bet the poster's database won't support the

    ... where (a,b) in (select x,y...)

    structure

    (what's that called, anyway? a row expression?)

    as far as i know, only oracle does

    yeah, it's in sql-92, but since when does every database vendor support sql-92, eh
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    So what is the alternative? I guess maybe:
    PHP Code:
    select *
    from   table,
           (
    select id1min(datemindate from table group by id1v
    where  t
    .id1 v.id1
    and    t.date v.mindate
    ...or is in-line view support unusual too?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    unusual? kinda

    i call that a derived table

    mysql certainly would have trouble with it, eh
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    alternatives? two

    correlated subquery:
    PHP Code:
    select ID1 
         
    ID2 
         
    Property1 
         
    Property2 
         
    myDate
      from myTable zzz
     where myDate 
    =
           ( 
    select min(myDate
               
    from myTable 
              where ID1 
    zzz.ID1 
    self-join:
    PHP Code:
    select t1.ID1 
         
    t1.ID2 
         
    t1.Property1 
         
    t1.Property2 
         
    t1.myDate
      from myTable t1
    inner
      join myTable t2  
        on t1
    .ID1 t2.ID1
    group
        by t1
    .ID1 
         
    t1.ID2 
         
    t1.Property1 
         
    t1.Property2 
         
    t1.myDate
    having t1
    .myDate min(t2.myDate
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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