Table Valued Parameter to filter first match for each row

Question

I have a table that looks like this:

id1 | id2 | value | id3
1   | abc | 23    | apt-1
1   | abc | 24    | apt-2
2   | def | 25    | apt-3
3   | def | 25    | apt-3

I need to get the first rows from the table above that match a table valued parameter like this:

id1 | id2 |
1   | abc |
2   | def |

I want the result to be

id1 | id2 | value | id3
1   | abc | 23    | apt-1
2   | def | 25    | apt-3

Instead I get back three rows in the table.

id1 | id2 | value | id3
1   | abc | 23    | apt-1
1   | abc | 24    | apt-2
2   | def | 25    | apt-3

How do I fix this? What would be the efficiency of this? This is an interim solution for when we write more data than currently being read, but will be reading based on id3 too in the future.

Let's call the table Foo, and the tvp tvpInput.

I tried the following two queries:

--Approach 1
SELECT *
FROM Foo as ret
INNER JOIN @tvpInput t
ON
t.id1 = ret.id1 
AND t.id2 = ret.id2

--Approach 2
SELECT *
FROM Foo AS ret
WHERE EXISTS 
(
    SELECT t.id1, t.id2 FROM @tvpInput t 
    WHERE 
    ret.id1 = t.id1
    AND ret.id2 = t.id2     
)

Let's just talk about 1, abc. How do you decided which row you want back? There are 2 rows that meet the criteria. Is is the lower value? Or does it matter which row you get back? There are a few ways this can be done and it somewhat depends on those answers. — Sean Lange, Nov 05 '15 at 20:34
That's a good point. I just want one that matched the criteria, it doesn't matter which one it is. — ame, Nov 05 '15 at 20:45

score 0 · Answer 1 · answered Nov 05 '15 at 20:25

0

If you want just a single row you can use TOP. Remember that there is no implied ordering in SQL tables so you would need to specify ORDER BY to receive determinate results e.g.

SELECT TOP 1 f.*
FROM Foo f
INNER JOIN @tvpInput t
ON t.id1 = ret.id1 AND t.id2 = ret.id2
ORDER BY f.id3

answered Nov 05 '15 at 20:25

innomatics

370
1
10

I want the first row that matches id1 and id2 for all the rows in the input tvp. Updating initial post to make that clearer. – ame Nov 05 '15 at 20:29

score 0 · Accepted Answer · answered Nov 05 '15 at 20:50

Here is one way you could do this. There are several other ways that would work too.

select *
from
(
    SELECT *, ROW_NUMBER() over(partition by ret.id1, ret.id2 order by (select newid())) as RowNum
    FROM Foo as ret
    INNER JOIN @tvpInput t ON t.id1 = ret.id1 
                AND t.id2 = ret.id2
) x
where x.RowNum = 1

Table Valued Parameter to filter first match for each row

2 Answers2