0

I have a table titled Company which has date-wise prices of all companies. I want to list the top n companies by price for each date, but the value of 'n' differs for different dates.

For example: I may need top 10 for 21st August 2014 but I may need top 20 for 22nd August 2014

I have the list of 'n' by dates in excel. Please suggest how to go about doing this

Vaibhav Agarwal
  • 25
  • 4
  • 1
    Please add table schema, sample data, your current query if you have one and your desired output, otherwise this is a bit too broad to answer. Try to give users that want to answer everything they need to write a useful solution. – Tanner Sep 23 '14 at 10:49

2 Answers2

1

Presuming DateCol is the Date-column, you can use a cte with ROW_NUMBER or DENSE_RANK(incl. ties)

WITH CTE AS
(
    SELECT c.*, rn = ROW_NUMBER() OVER ( PARTITION BY DateCol ORDER BY Price DESC )
    FROM dbo.Company c
)
SELECT * FROM CTE 
WHERE  rn > 0 AND rn <= CASE DateCol 
                          WHEN '20140821' THEN 10
                          WHEN '20140822' THEN 20
                          ELSE 10   -- default
                        END

If it's actually a DateTime column and you have to truncate the time:

How can I group by date time column without taking time into consideration

Community
  • 1
  • 1
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
0

Lets Try the below Script,

If (select COUNT(*) from Company where date=21st August 2014)>0
begin
select top 10 * from Company  where date=21st August 2014
end

If (select  COUNT(*) from Company where date= 22nd August 2014)>0
begin

select top 20 * from Company where date= 22nd August 2014
end
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
RickyRam
  • 181
  • 1
  • 1
  • 10