Select Top n from multiple date values

Hello,

I have the following format:
Date,Equity,Systemident

I have multiple entries for the same date. Key will be systemident.
How can I select the top n value for a certain date interval?
So on each day I need the top n value for Equity.
For example top 5
On each day I need to have the top 5 equity.

Do I need a procedure or can it be a query?
SchuttendAsked:
Who is Participating?
 
dportasConnect With a Mentor Commented:
Or:

SELECT [Date], Equity
FROM
 (SELECT [Date], Equity,
  RANK() OVER (PARTITION BY [Date] ORDER BY Equity DESC) rnk
  FROM tbl) t
WHERE rnk <= 5;
0
 
dportasCommented:
I will assume that "Date" is a DATE type column. Hopefully "Date" isn't the actual name you'll use for the column.

SELECT [Date], Equity
FROM
 (SELECT [Date], Equity,
  RANK() OVER (PARTITION BY [Date] ORDER BY Equity) rnk
  FROM tbl) t
WHERE rnk <= 5;

Try it with both RANK() and DENSE_RANK() and decide which one meets your requirements.
0
 
SchuttendAuthor Commented:
Hi,

Almost getting there. It's need to be in the correct [Date] order. Now the results of jump up and down with dates. Like:
01/01/2000 -- result
01/05/2003 -- results
01/1/2001 - results

Maybe something with Group by ??
0
 
dportasCommented:
Just add ORDER BY [Date]
0
 
SchuttendAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.