min(value) and Max(value) for the particular modified date

Question

How to Get a Min and Max column value for the Modified date

I want to select top and bottom column value for the particular date.

Table:

 **DATE**     **MODIFIED DATE**  **VALUE**

03:02:2009  04:02:2009 100
03:02:2009  04:02:2009 200
03:02:2009  04:02:2009 400
04:02:2009  05:02:2009 150
04:02:2009  05:02:2009 200
04:02:2009  05:02:2009 500

In my Table - Date Column and Value column is there

How can i get min(value) and Max(value) for the particular modified date?

Modified date should be n number of date, suppose am giving from this modified date to this modifed date

I want output like this

MODIFIED DATE   MIN VALUE MAX VALUE
04:02:2009  100 400
05:02:2009  150 500

Answer 1

SELECT ModifiedDate, MIN(Value), MAX(Value)
FROM TheTable
GROUP BY ModifiedDate
ORDER BY ModifiedDate

---

edit re comment; it isn't clear how you want to calculate ModifiedDate, but for a simple formula, just inline it:

SELECT DATEADD(day, 1, Date) AS [ModifiedDate], MIN(Value), MAX(Value)
FROM TheTable
GROUP BY DATEADD(day, 1, Date)
ORDER BY DATEADD(day, 1, Date)

For something more complex, anything from:

• table variables (@table)
• temporary tables (#table)
• sub-queries
• etc

Answer 2

Marc Gravell,

Table

DATE    VALUE
22:02:2009  200
22:02:2009  400
22:02:2009  100
24:02:2009  250
24:02:2009  400
24:02:2009  800

(Select Date, … from table)

While executing the above query, below mentioned table is displaying

DATE    MODIFIED DATE VALUE
22:02:2009  23:02:2009 200
22:02:2009  23:02:2009 400
22:02:2009  23:02:2009 100
24:02:2009  25:02:2009 250
24:02:2009  25:02:2009 400
24:02:2009  25:02:2009 800

Here leave the date column; exactly I need min (value) and max (value) for the Modified Date

Output I need like this

DATE    MN VALUE MAX VALUE
22:02:2009  200 100
24:02:2009  250 800

Note: Don’t evaluate the column value, only I need top value and bottom value for the particular date.