asked on

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 )

Patrick Matthews

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

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

soriega

ASKER

Thanks, that seems to do the job