SQL MAX(SELECT TOP 5 PRICE)

Hi. I need a SQL Query that retreives the Max value during the last 5 days, specifically the largest value of today and 4 previous days. Here is an example:

I have these values:

Date;MYVALUE
2009-01-01;135
2009-01-02;133
2009-01-03;134
2009-01-04;132
2009-01-05;131
2009-01-06;133
2009-01-07;138
2009-01-08;133

I want this to return
2009-01-05;135 ( the value 135 is the largest value of the 5 day period which comes from 2009-01-01 )
2009-01-06;134 ( the value 134 is the largest value of the 5 day period which comes from 2009-01-03 )
2009-01-07;138 ( the value 138 is the largest value of the 5 day period which comes from 2009-01-07 )
2009-01-08;138 ( the value 138 is the largest value of the 5 day period which comes from 2009-01-07 )

DatabasesMicrosoft SQL ServerSQL

Last Comment

soriega

8/22/2022 - Mon

Patrick Matthews

10/4/2009

SELECT CONVERT(datetime, '2009-01-05') AS [Date], MAX(MyValue) AS MyValue
FROM SomeTable
WHERE [Date] BETWEEN '2009-01-01' AND '2009-01-05'
UNION
SELECT CONVERT(datetime, '2009-01-06') AS [Date], MAX(MyValue) AS MyValue
FROM SomeTable
WHERE [Date] BETWEEN '2009-01-02' AND '2009-01-06'
UNION
SELECT CONVERT(datetime, '2009-01-07') AS [Date], MAX(MyValue) AS MyValue
FROM SomeTable
WHERE [Date] BETWEEN '2009-01-03' AND '2009-01-07'
UNION
SELECT CONVERT(datetime, '2009-01-08') AS [Date], MAX(MyValue) AS MyValue
FROM SomeTable
WHERE [Date] BETWEEN '2009-01-04' AND '2009-01-08'

soriega

10/4/2009

ASKER

That might work except that if i need to do it for 100 000 rows, it will be to complicated... Any other more general suggestion ?

ASKER CERTIFIED SOLUTION

Patrick Matthews

10/4/2009

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial