CODEWITHSUNDEEP
sql-server
Amir
Amir

Reputation: 2022

SQL Server: How to select top rows of a group based on value of the column of that group?

I have two tables like below.

table 1

id rem   
1   2
2   1

table 2

id value
1   abc
1   xyz
1   mno
2   mnk
2   mjd

EDIT:

#output
id value
1  abc
1  xyz
2  mnk

What i want to do is select top 2 rows of table2 with id one as rem value is 2 for id 1 and top 1 row with id 2 as its rem value is 1 and so on. I am using MS sqlserver 2012 My whole scenario is more complex than this. Please help.
Thank you.
EDIT : I know that i should have given what i have done and how i am doing it but for this particular part i don't have idea for starting. I could do this by using while loop for each unique id but i want to do it in one go if possible.

Upvotes: 2

Views: 2288

Answers (2)

Dheeraj Sharma
Dheeraj Sharma

Reputation: 709

Try This:

DECLARE @tbl1 TABLE (id INT, rem INT)
INSERT INTO @tbl1 VALUES (1,   2), (2,   1)

DECLARE @tbl2 TABLE (id INT, value VARCHAR(10))
INSERT INTO @tbl2 VALUES  (1,   'abc'), (1,   'xyz'),
(1,   'mno'), (2,   'mnk'), (2,   'mjd')

SELECT * FROM @tbl1 -- your table 1
SELECT * FROM @tbl2 -- your table 2

SELECT id,value,rem FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY T.ID ORDER BY T.ID) rowid,
T.id,T.value,F.rem FROM  @tbl2 T LEFT JOIN @tbl1 F ON T.id = F.id ) A WHERE rowid = 1 
-- your required output

Hope it helps.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269473

First, SQL tables represent unordered sets. There is no specification of which values you get, unless you include an order by.

For this purpose, I would go with row_number():

select t2.*
from table1 t1 join
     (select t2.*,
             row_number() over (partition by id order by id) as seqnum
      from table2 t2
     ) t2
     on t1.id = t2.id and t2.seqnum <= t1.rem;

Note: The order by id in the windows clause should be based on which rows you want. If you don't care which rows, then order by id or order by (select null) is fine.

Upvotes: 1

Related Questions