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

If you can add to a temp table and then query from that, you can do it in one go.

WITH T AS (temp table select), RN AS (select min row-numbers from T) SELECT T.NAME, T.DATA1, T.DATA2 FROM T INNER JOIN RN on T.row_number = RN.row_number

There are many other ways to write this, but that's how I've been doing similar things.

Answer 2

The tag of this question indicates that it would be a solution for DB2, but this is very similar to MS-SQL server, if so try these solutions:

Using CROSS, it will be possible to display what exists only in both tables

select A.*, B.DATA1, B.DATA2
from A
cross apply (select top 1 * from B where B.name = A.name) B

But it is possible to change to OUTER to display what exists in A without the obligation to exist in B

select A.*, B.DATA1, B.DATA2
from A
OUTER apply (select top 1 * from B where B.name = A.name) B

In the structure of the apply statement, it would also be possible to include an ORDER statement, since there is no indication of the order of exits in table B

Answer 3

You can use row number to get one row for each name, try something like below

Select name,data1,data2 from 
(Select A.NAME,B.DATA1,B.DATA2,row_number() over(partitioj by a.name order by a.name) rn
From A 
Inner Join B on A.NAME = B.NAME) where rn=1

Answer 4

Try to dedupe B like this

SELECT  A.NAME, bb.DATA1, bb.DATA2 
FROM    A 
JOIN    B bb
ON      A.NAME = B.NAME
WHERE   NOT EXISTS (SELECT  *
                    FROM    B
                    WHERE   NAME = bb.NAME
                            AND (DATA1 > bb.DATA1
                                OR DATA1 = bb.DATA1 AND DATA2 > bb.DATA2))

Add more OR clauses if more DATAx columns exist.

If A contains duplicates too, simply use DISTINCT as in the OP.

Answer 5

This will work:

with temp as (
    select A.NAME, B.DATA1, B.DATA2, 
        row_number() over (partition by A.NAME order by A.NAME) as rownum
    from TABLEA A inner join TABLEB B
    on A.NAME = B.NAME
)
select NAME, DATA1, DATA2 from temp where rownum = 1

If you want to select the least value of data1 and within it data2, then use this variation:

with temp as (
    select A.NAME, B.DATA1, B.DATA2, 
        row_number() over (partition by A.NAME order by B.DATA1, B.DATA2) as rownum
    from TABLEA A inner join TABLEB B
    on A.NAME = B.NAME
)
select NAME, DATA1, DATA2 from temp where rownum = 1

Both the queries will give one row per name.

Answer 6

Not sure if this will solve your problem or not, but you could try using the GROUP BY clause and group by one of the name columns.

DB2 Group by tutorial