SQL Server Performance

LEFT OUTER JOIN, joining with FIRST ROW ONLY

Discussion in 'General Developer Questions' started by IdRatherBeProgramming, Apr 28, 2004.

  1. Is there a way to do a left outer join and ONLY pull back 1 row? Standard joins pull back all rows that match the criteria, but I only want the first one.

    Table1:
    ID myName
    1 Jason
    2 Tony
    3 Bill

    Table2:
    ID myDate
    1 01/01/2004
    1 01/12/2004
    3 01/30/2004

    The following is a standard LEFT OUTER JOIN results:

    SELECT T1.myName, T2.myDate FROM Table1 T1 LEFT OUTER JOIN Table2 T2 ON T1.ID = T2.ID ORDER BY T1.ID, T2.Date DESC

    myName myDate
    Jason 01/12/2004
    Jason 01/01/2004
    Tony NULL
    Bill 01/30/2004

    What I want is the follwing results:

    myName myDate
    Jason 01/12/2004
    Tony NULL
    Bill 01/30/2004
  2. ChrisFretwell New Member

    Try

    SELECT T1.myName, min(T2.myDate) as mydate
    FROM Table1 T1
    LEFT OUTER JOIN Table2 T2 ON T1.ID = T2.ID
    group by t1.myname
    ORDER BY T1.ID, min(T2.Date) DESC


    This will return 1 row for each name, and the lowest date (null if there is none)
  3. Thanks. But there's more to it than aggregation... I guess I should have been a little more clear with my example. Let's pretend that Table2 has multiple columns and I want them all to show in the result set:

    Table2:
    ID myDate myCode myText
    1 01/01/2004 03 Hi
    1 01/12/2004 01 Yo
    3 01/30/2004 01 Billy-Bob

    What I want is Table 1, joined with the first row of Table 2 that meets the critera (let's say, the min(myDate) as in the reply above)...

    Therefore, my resulting table would need to be:

    myName myDate myCode myText
    Jason 01/12/2004 01 Yo
    Tony NULL NULL NULL
    Bill 01/30/2004 01 Billy-Bob

    I am aware of the aggregate functions Min, Max, Sum, etc. However, using them would potentially give me different columns from different rows in my result set (MIN(myDate), MIN(myCode) would yeild data from different rows in Table2, when I want the data to stay together). I want myDate to be the "Anchor" and pull in it's entire row of data to match to Table1 by the ID.

    If this is confusing, I will explain what I have to do now, and you might get a clearer idea of what I'm looking for:

    Currently, I just query Table1 with a LEFT OUTER JOIN on Table2 ON ID and ORDER BY ID, myDate DESC. As I loop through the result set, I read the first row for each distinct ID and display it on the screen. Each subsequent record for the same ID I already displayed is skipped in the recordset. Therefore, my final results on the screen are what I want, but I do the work, not the SQL Server Engine.

    I want SQL Server to pull the recordset back with Table1 and the FIRST match of Table2 Only.

    Any ideas?
  4. gaurav_bindlish New Member

    I think this query can be written using a join of the main table with a derived table from the other table using TOP option. I am not so good at this so probably I'll wait for others to post the query.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  5. ChrisFretwell New Member

    yup. Exactly like Guarav said....(I think this is okay, but may have missed a bracket somewhere)

    SELECT T1.myName, T4.myDate, t4.mycode, t4.mytext
    FROM Table1 T1
    LEFT OUTER JOIN (
    select t3.id,[date],mycode,mytext from Table2 T3
    join (select id,min(date) as mindate from table2 group by id) t2
    ON T3.ID = T2.ID and t3.date=t2.mindate) T4 on t1.id = t4.id
    group by t1.myname
    ORDER BY T1.ID, t4.mydate DESC

  6. gaurav_bindlish New Member

    No offence to others.... When it comes to writing TSQL, I generally look for answers from Bambola and Chris....

    I don't understand why there so few gentleman on this forum who try to write TSQL?

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  7. harsh_sr New Member

    A modified version of Chris' query w/o errors is

    SELECT T1.myName, T4.myDate
    FROM Table1 T1 LEFT OUTER JOIN (
    SELECT T3.ID,T3.mydate
    FROM Table2 T3 JOIN
    (SELECT ID, MAX(mydate) maxdate FROM table2 GROUP BY ID) T2
    ON T3.ID = T2.ID AND T3.mydate = T2.maxdate
    ) T4
    ON T1.id = T4.id
    ORDER BY T1.ID, T4.mydate DESC

    OR

    SELECT T1.ID, T1.myName, MAX(T2.myDate) mydate
    FROM Table1 T1
    LEFT OUTER JOIN Table2 T2 ON T1.ID = T2.ID
    GROUP BY t1.ID, T1.myName
    ORDER BY T1.ID, T2.myDate DESC

    Both of these will give you same result.
    Again, I think Aggerigate functions will not pick values from different rows in above examples.

    HTH
    Harsh

Share This Page