Select the first row in a join of two tables in one statement
- by Oscar Cabrero
hi 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 cannt 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 the select the Min Id per name.
the problem here is that i require to do this in one single statement.
this is a DB2 database thanks
