CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Developer Nation
Developer Nation

Reputation: 384

SQL - How to get Top 5 record with sub-record

I have a query that returns the following table

ID   SubId  Rate    Time
1    1      10.00   '00:00:10'
2    1      11.00   '00:00:15'
3    2      12.00   '00:00:20'
4    3      13.00   '00:00:25'
5    4      14.00   '00:00:30'
6    5      15.00   '00:00:35'
7    6      16.00   '00:00:40'

Now the problem is that i need all those record whose SubId lies in Top 5 of Time's order.

ID   SubId  Rate    Time
1    1      10.00   '00:00:10'
2    1      11.00   '00:00:15'
3    2      12.00   '00:00:20'
4    3      13.00   '00:00:25'
5    4      14.00   '00:00:30'
6    5      15.00   '00:00:35'

My Approach

Select ID,SubId,Rate from Query1 where SubId In (Select Top 5 SubId from Query1)
--Time was not included in it

Note : Please do not suggest an answer like above because it needs to use the query twice as the query is already taking too much time to return the above records.

Upvotes: 0

Views: 422

Answers (3)

SouravA
SouravA

Reputation: 5253

My answer is only slightly different than Felix's with a small difference. I would rather create a covered NC Index. That way I/O operations would get reduced when it's used down the line.

Store the results once in a temporary table and create a covered non-clustered on SubID

Select ID, SubId, Rate, [Time]
INTO #results
FROM Query1


CREATE NONCLUSTERED INDEX IX_SubID ON #results(SubId) INCLUDE(Id, Rate, [Time])

SELECT A.ID, A.SubId, A.Rate, A.[Time]
FROM 
#results  A
JOIN
(SELECT TOP 5 SubID from #results order by [Time] desc)  B
on A.SubID  =   B.SubID

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

with x as 
(select row_number() over(order by time) as rn, * from tablename)
select ID,SubId,Rate from x where rn <=5

This will assign row numbers based on ascending order of time in your table. You can also partition and order by your desired columns. Thereafter, you can select whatever row numbers from the cte you need.

Upvotes: 1

Felix Pamittan
Felix Pamittan

Reputation: 31879

If you don't want to use the same query twice, I suggest you insert the result into a temporary table. That way, you don't have to execute the complex query twice.

CREATE TABLE #TopFive(Id)
INSERT INTO #TopFive
    SELECT TOP 5 SubId FROM QueryId ORDER BY [Time] DESC

Then in your subsequent queries, you can just use the temporary table:

SELECT * FROM <tbl> WHERE subId IN(SELECT Id FROM #TopFive)

You could also add a NONCLUSTERED INDEX on the temporary table for added performance gain:

CREATE NONCLUSTERED INDEX NCI_TopFive ON #TopFive(Id)

Upvotes: 1

Related Questions