CODEWITHSUNDEEP
sqlsql-servert-sql
Louis S. Berman
Louis S. Berman

Reputation: 994

T-SQL Ranking w/First, Min, Max, Last

HI! I have an SQL Server table that contains the following fields:

DateTime DATETIME
Price FLOAT

The table has a unique DateTime key. I'd like to write a query that returns the data in 20-row chunks returning the FIRST, MAX, MIN, and LAST Price for each set of 20 rows.

Any help in this regard would be greately appreciated...

Upvotes: 1

Views: 2195

Answers (3)

Steve Kass
Steve Kass

Reputation: 7184

I believe your first group contains only 19 rows, not 20 (because row numbers start at 1, not 0). This is easy to fix - just subtract 1 from the ROW_NUMBER().

I'm also providing an alternative solution below that will probably be considerably more efficient (because it has no subqueries to implement as nested loops), and it might also be more manageable.

Note: If the number of rows in "Prices" is not exactly divisible by 20, both Sam's and my solution will return the first, min, max, and last prices for one group with fewer than 20 items - that group contains the most recent items. (It's a good idea to test a query like this using a number of rows not divisible by 20...)

DECLARE @groupsize INT = 20;
WITH PricesWithRkGp(PurchaseDate,Price,RkUp,RkDn,Gp) AS (
  SELECT
    PurchaseDate,
    Price,
    -1+ROW_NUMBER() OVER (ORDER BY PurchaseDate),
    -1+ROW_NUMBER() OVER (ORDER BY PurchaseDate DESC),
    (-1+ROW_NUMBER() OVER (ORDER BY PurchaseDate)) / @groupsize
  FROM Prices
)
  SELECT
    MIN(Price) AS MinPrice,
    MAX(Price) AS MaxPrice,
    Gp,
    MAX(CASE WHEN RkUp%@groupsize = 0 THEN Price ELSE NULL END) AS FirstPrice,
    MAX(CASE WHEN RkUp%@groupsize = @groupsize - 1 OR RkDn = 0 THEN Price ELSE NULL END) AS LastPrice
  FROM PricesWithRkGp
  GROUP BY Gp
  ORDER BY Gp;

Upvotes: 1

subhash
subhash

Reputation: 286

You can do this using ROW_NUMBER function of CTE. Here is the code with details:

http://blog.sqlauthority.com/2007/06/11/sql-server-2005-t-sql-paging-query-technique-comparison-over-and-row_number-cte-vs-derived-table/

Upvotes: 0

Sam Saffron
Sam Saffron

Reputation: 131112

Do you mean something like this: 

select 
    *,
    (select Price from Prices where PurchaseDate = [Start]) as [First Price],
    (select Price from Prices where PurchaseDate = [Finish]) as [Last Price]
from
(
    select 
        MIN(PurchaseDate) as [Start], 
        MAX(PurchaseDate) as [Finish], 
        MIN(Price) as [Min Price], 
        MAX(Price) as [Max Price], 
        AVG(Price) as [Average Price]
        from 
    (
        select (ROW_NUMBER() OVER (ORDER BY PurchaseDate)) / 20 as [Seq], *
        from Prices
    ) as X 
    group by X.Seq
) as Y

Note, I used the following to generate data: 

create table Prices (
PurchaseDate DATETIME primary key,
Price FLOAT
)

go

declare @records int
declare @date datetime 
declare @price float

set @records = 1000

while @records > 0 
begin 

    set @date = GETDATE() - cast((RAND() * 10000) as int)
    set @price = RAND() * 10000     

    if not exists(select 1 from Prices where PurchaseDate = @date) 
    begin 
        insert Prices values (@date, @price) 
        set @records = @records - 1
    end
end

Upvotes: 0

Related Questions