Need help in query

[Saiyed Alam]

I have a table which is some thing like below..

Date ID

2009-07-01 1
2009-07-01 2
2009-07-01 3
2009-08-01 4
2009-08-01 5
2009-08-01 6
2009-09-01 7
2009-09-01 8
2009-10-01 9
2009-10-01 10
2009-11-01 11

.... Now I need to write a query which will show a output like below.

Date Start End
2009-07 1 3
2009-08 4 6
2009-09 7 8

... How can I do this.. Any help would be highly appreciated Thanking In Advance Johnny

[J4amieC]

Thats about the easiest type of aggregate you can hope for. Simple MIN and MAX over a grouped set:

SELECT [date],
MIN([id]),
MAX([id])
FROM [YourTableName]
GROUP BY date

[Wayne Gaylard]

Hi, Try this

SELECT DISTINCT d.Date1 as `ActionDate`, (SELECT MIN(ID) FROM dates WHERE Date1 = `ActionDate`) as `Start`, (SELECT MAX(ID) FROM dates WHERE Date1 = `ActionDate`) as `End` FROM dates d;

Happy Coding This assumes table is called dates and date column is called date1

[David Skelly]

He doesn't want to group by date, he wants to group by month so it is a little more complicated. (And before you ask, no, I don't know what the solution is.)

[J4amieC]

So he did, I missed that. Still easy though:

SELECT MONTH(date),YEAR(date),
MIN([id]),
MAX([id])
FROM [YourTableName]
GROUP BY MONTH(date),YEAR(date)

and if you really must mash that first column back to the format he wanted

SELECT
CAST(YEAR(date) AS VARCHAR(4)) + '-' + CAST(MONTH(date) AS VARCHAR(2)),
MIN([id]),
MAX([id])
FROM [YourTableName]
GROUP BY MONTH(date),YEAR(date)

[J4amieC]

This gives the same result as mine above, but is far less readable and far less efficient. Get friendly with the GROUP BY functionality of sql if you want to aggregate data.

[Saiyed Alam]

Thanks It did work..