Select the first row in a join of two tables in on

Question

I need to select only the first row from a query that joins tables A and B. On table B exist multiple records with same name. There are not identifiers in any of the two tables. I cannot change the scheme either because I do not own the DB.

TABLE A
NAME

TABLE B
NAME
DATA1
DATA2

Select Distinct A.NAME,B.DATA1,B.DATA2 
From A 
Inner Join B on A.NAME = B.NAME

This gives me

NAME       DATA1    DATA2
sameName   1        2
sameName   1        3
otherName  5        7
otherName  8        9

but I need to retrieve only one row per name

NAME       DATA1    DATA2
sameName   1        2
otherName  5        7

I was able to do this by adding the result into a temp table with a identity column and then select the minimum id per name.

The problem here is that I require to do this in one single statement.

Answer 1

Using a GROUP BY may get you part way there, but beware. If you do something like this:

Select A.NAME, min(B.DATA1), min(B.DATA2) 
From A Inner Join B on A.NAME = B.NAME 
Group by A.NAME;

You will get the result you are looking for:

  NAME      DATA1   DATA2
  sameName   1        2    
  otherName  5        7

But only because of the data you are testing with. If you change the data, so that instead of:

otherName  8        9

you had:

otherName  8        4

It would return:

  NAME      DATA1   DATA2
  sameName   1        2    
  otherName  5        4

Note that otherName does not return DATA1 and DATA2 from the same record!

Update: A self-join with a comparison on one of the data values may help you, such as:

SELECT a.*, b.* FROM a,b 
   LEFT JOIN b b2 ON b.name = b2.name AND b.data2 < b2.data2 
   WHERE a.name = b.name AND b2.data2 IS NOT NULL;

However, this will only work if the values in DATA2 are unique per NAME.

Answer 2

SELECT  A.NAME, bb.DATA1, bb.DATA2 
From A Inner Join B on A.NAME = B.NAME
WHERE B.DATA1 = (SELECT MIN(DATA1) FROM B WHERE NAME = A.NAME)

This will give your desired result, providing B.DATA1 values are unique within the set relating to table A.

If they're not unique, the only other way I know is using CROSS APPLY in MSSQL 2005 and above.