[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 774
  • Last Modified:

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?
0
Schuttend
Asked:
Schuttend
  • 3
  • 2
1 Solution
 
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
 
dportasCommented:
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
 
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now