April 5, 2017 at 7:51 am
Hi. I hope I can explain my problem. I join two table. Result is like below:
Id ProgramId Index Point
1 1 1 20
2 1 2 30
3 1 3 10
4 2 1 5
5 2 2 10
6 2 3 10
7 2 3 10
and go on.
I want to take two record for each programId by index. So, I want to write a query that give me data like below:
Id ProgramId Index Point
1 1 3 10
2 1 2 30
6 2 3 10
5 2 2 10
How can I write a query that give me only two record for each ProgramId value.
Thanks in advance
April 5, 2017 at 8:11 am
Your expected results do not match your supplied sample data.
For this reason, I am unable to create a tested result. However, the code below, should get ypu started.
CREATE TABLE #TestData (
Id INT NOT NULL
, ProgramId INT NOT NULL
, [Index] INT NOT NULL
, Point INT NOT NULL
);
INSERT INTO #TestData ( Id, ProgramId, [Index], Point )
VALUES ( 1, 1, 1, 20 )
, ( 2, 1, 2, 30 )
, ( 3, 1, 3, 10 )
, ( 4, 2, 1, 5 )
, ( 5, 2, 2, 10 )
, ( 6, 2, 3, 10 )
, ( 7, 2, 3, 10 );
GO
SELECT
Id
, ProgramId
, [Index]
, Point
FROM (
SELECT
Id
, ProgramId
, [Index]
, Point
-- This is where you need to provide the logic to decide which 2 records to return.
, rn = ROW_NUMBER() OVER (PARTITION BY ProgramId ORDER BY [Index] DESC, Point)
FROM #TestData
) AS src
WHERE src.rn<= 2;
April 5, 2017 at 9:17 am
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply