Query For Sorting and group by based on 2 columns

[abi_learn_vba]

Hi,

I have below table which has data arranged in sequential order of date.

Table1</SPAN>
Date</SPAN>	ID</SPAN>
2/1/2013</SPAN>	A3</SPAN>
2/2/2013</SPAN>	A3</SPAN>
2/3/2013</SPAN>	A3</SPAN>
2/4/2013</SPAN>	A3</SPAN>
2/5/2013</SPAN>	A3</SPAN>
2/6/2013</SPAN>	A3</SPAN>
2/7/2013</SPAN>	A3</SPAN>
2/8/2013</SPAN>	A3</SPAN>
2/9/2013</SPAN>	A3</SPAN>
2/10/2013</SPAN>	A1</SPAN>
2/10/2013</SPAN>	A3</SPAN>
2/11/2013</SPAN>	A1</SPAN>
2/11/2013</SPAN>	A3</SPAN>
2/12/2013</SPAN>	A1</SPAN>
2/13/2013</SPAN>	A1</SPAN>
2/14/2013</SPAN>	A1</SPAN>
2/15/2013</SPAN>	A1</SPAN>
2/16/2013</SPAN>	A1</SPAN>
2/17/2013</SPAN>	A1 </SPAN>

<TBODY>
</TBODY>

Here i need Sort by Date and then Group by the ID together that my results of the query should look like the one below. Where A3 ID is grouped together until 2/11/2013 and A1 ID starts from 2/10/2013.

Query Results</SPAN>
Date</SPAN>	ID</SPAN>
2/1/2013</SPAN>	A3</SPAN>
2/2/2013</SPAN>	A3</SPAN>
2/3/2013</SPAN>	A3</SPAN>
2/4/2013</SPAN>	A3</SPAN>
2/5/2013</SPAN>	A3</SPAN>
2/6/2013</SPAN>	A3</SPAN>
2/7/2013</SPAN>	A3</SPAN>
2/8/2013</SPAN>	A3</SPAN>
2/9/2013</SPAN>	A3</SPAN>
2/10/2013</SPAN>	A3</SPAN>
2/11/2013</SPAN>	A3</SPAN>
2/10/2013</SPAN>	A1</SPAN>
2/11/2013</SPAN>	A1</SPAN>
2/12/2013</SPAN>	A1</SPAN>
2/13/2013</SPAN>	A1</SPAN>
2/14/2013</SPAN>	A1</SPAN>
2/15/2013</SPAN>	A1</SPAN>
2/16/2013</SPAN>	A1</SPAN>
2/17/2013</SPAN>	A1</SPAN>

<TBODY>
</TBODY>

I tried many using Order by and group by without success, can any one help me with a query for this?

Thanks in Advance
-Abi

 

[Joe4]

OK. Obviously, the situation I was describing (which I thought could happen) would be much more complex, and I don't think could be done with straight SQL code.
I am glad that Angel figured out what you were asking on his second reply and got you the code that you need.

 

[AngelJ]

AngelJ,

That was awesome. This is what exactly i was looking for.

Thank you very much for your help.

Thanks & Regards
-Abi

You're welcome! I'm glad that worked for you. Just to be clear, that query first sorts by the earliest date that occurs per ID, then by date, which is how I understood your request. So if two records were added as Joe4 said in post #9, you'd get this:

Table1	Query Results
Date ID 2/1/2013 A3 2/2/2013 A3 2/3/2013 A3 2/4/2013 A3 2/5/2013 A3 2/6/2013 A3 2/7/2013 A3 2/8/2013 A3 2/9/2013 A3 2/10/2013 A1 2/10/2013 A3 2/11/2013 A1 2/11/2013 A3 2/12/2013 A1 2/13/2013 A1 2/14/2013 A1 2/15/2013 A1 2/16/2013 A1 2/17/2013 A1 2/18/2013 A3 2/19/2013 A3 <tbody> </tbody>	Date ID 2/1/2013 A3 2/2/2013 A3 2/3/2013 A3 2/4/2013 A3 2/5/2013 A3 2/6/2013 A3 2/7/2013 A3 2/8/2013 A3 2/9/2013 A3 2/10/2013 A3 2/11/2013 A3 2/18/2013 A3 2/19/2013 A3 2/10/2013 A1 2/11/2013 A1 2/12/2013 A1 2/13/2013 A1 2/14/2013 A1 2/15/2013 A1 2/16/2013 A1 2/17/2013 A1 <tbody> </tbody>

<tbody>
</tbody>

Does that look ok?

 

[AngelJ]

To add to my previous post, I know you said the ID won't change back in the same month. But the query will handle it the same even if it changes back to A3 in March, or even later, which is why I ask.

 

[abi_learn_vba]

To add to my previous post, I know you said the ID won't change back in the same month. But the query will handle it the same even if it changes back to A3 in March, or even later, which is why I ask.

Yes it absolutely looks perfect... Excellent..

 

[AngelJ]

Great, just making sure.