CODEWITHSUNDEEP
t-sql
Peter
Peter

Reputation: 505

1th and 7th row in grouping

I have this table named Samples. The Date column values are just symbolic date values.

+----+------------+-------+------+
| Id | Product_Id | Price | Date |
+----+------------+-------+------+
|  1 |          1 |   100 |    1 |
|  2 |          2 |   100 |    2 |
|  3 |          3 |   100 |    3 |
|  4 |          1 |   100 |    4 |
|  5 |          2 |   100 |    5 |
|  6 |          3 |   100 |    6 |
...
+----+------------+-------+------+

I want to group by product_id such that I have the 1'th sample in descending date order and a new colomn added with the Price of the 7'th sample row in each product group. If the 7'th row does not exist, then the value should be null.

Example:

+----+------------+-------+------+----------+
| Id | Product_Id | Price | Date | 7thPrice |
+----+------------+-------+------+----------+
|  4 |          1 |   100 |    4 | 120      |
|  5 |          2 |   100 |    5 | 100      |
|  6 |          3 |   100 |    6 | NULL     |
+----+------------+-------+------+----------+

I belive I can achieve the table without the '7thPrice' with the following

SELECT * FROM (
    SELECT ROW_NUMBER() OVER (PARTITION BY Product_Id ORDER BY date DESC) r, * FROM Samples
) T WHERE T.r = 1

Any suggestions?

Upvotes: 0

Views: 39

Answers (1)

SQLChao
SQLChao

Reputation: 7847

You can try something like this. I used your query to create a CTE. Then joined rank1 to rank7.

 ;with sampleCTE 
   as 
  (SELECT ROW_NUMBER() OVER (PARTITION BY Product_Id ORDER BY date DESC) r, * FROM Samples)

select * 
from
  (select * from samplecte where r = 1) a
left join
  (select * from samplecte where r=7) b
on a.product_id = b.product_id

Upvotes: 1

Related Questions