CODEWITHSUNDEEP
sql-serverselect
Diego
Diego

Reputation: 2362

SQL Server select first, last from with condition for each group

I have a SQL Server Query that joins several tables and retrieves columns from different tables.

I need to return the first and last record of each group of sets...

Data

ID      Name    Number  Enabled     Date
-----------------------------------------------
1       AAA     1234    true        12/10/2016
1       BBB     2222    true        12/10/2016
1       CCC     3333    true        12/10/2016
1       DDD     4444    true        12/10/2016
2       EEE     3453    true        12/10/2016
2       FFF     4453    true        12/10/2016
2       GGG     5222    true        12/10/2016
2       HHH     6344    true        12/10/2016
3       WWW     3453    true        12/10/2016
3       DDD     6453    true        12/10/2016
3       MMM     7222    true        12/10/2016
3       GGG     8344    true        12/10/2016

It has an ORDER BY ID, and Number

Upvotes: 0

Views: 451

Answers (1)

TriV
TriV

Reputation: 5148

You could use ROW_NUMBER like this

;WITH temp AS
(
    SELECT *,
            Row_number() over(PARTITION BY ID ORDER BY Number) AS RnAsc,
            Row_number() over(PARTITION BY ID ORDER BY Number Desc) AS RnDesc
    FROM @SampleData sd 
)
SELECT * 
FROM temp t
WHERE t.RnAsc = 1  -- first row
      OR t.RnDesc = 1 -- last row

Upvotes: 1

Related Questions