CODEWITHSUNDEEP
sqlsql-server-2008t-sql
user3219693
user3219693

Reputation: 201

first and last row_number

I have a table with many different companies, each with a number of orders ranging from only 1 to a possible n

I have used 

ROW_NUMBER() OVER (PARTITION BY CompanyName ORDER BY OrderDate) AS Orders

This gives me a sample like below

Comp1   1
Comp2   1
Comp3   1
Comp3   2
Comp3   3
Comp3   4
Comp4   1
Comp4   2
Comp4   3

How do I go through the table and select the first and last record for each company? to end up with:

Comp1   1
Comp2   1
Comp3   1
Comp3   4
Comp4   1
Comp4   3

Upvotes: 0

Views: 714

Answers (2)

guthy
guthy

Reputation: 326

select * from Orders
where (CompanyName , OrderDate) in (
    select CompanyName , min(OrderDate) from Orders group by CompanyName 
    union all
    select CompanyName , max(OrderDate) from Orders group by CompanyName 
)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270371

You can use row_number() twice or count():

select c.*
from (select c.*,
             row_number() over (partition by CompanyName order by OrderDate) as seqnum,
             count(*) over (partition by CompanyName) as cnt
      from companies c
     ) c
where seqnum = 1 or seqnum = cnt;

Upvotes: 4

Related Questions