CODEWITHSUNDEEP
sqlsql-servergroup-by
Ketchup
Ketchup

Reputation: 3111

Ordering Ordered Groups

I have a table structure similar to the following:

ID  |DateUploaded|FamilyCode|
1   |01/01/2010  |FC0001    |
2   |01/01/2011  |FC0001    |
3   |01/01/2012  |FC0001    |
4   |01/08/2012  |FC0002    |
5   |01/01/2012  |FC0002    |
6   |01/08/2012  |FC0002    |
7   |01/01/2012  |FC0002    |
8   |01/08/2012  |FC0003    |
9   |01/07/2012  |FC0003    |

I have a select which has the following ORDER BY:

 ORDER BY FamilyCode, DateUploaded desc

This sorts the results so they are effectively grouped into families and the the elements of the families are sorted by the DateReceived. What is now required however is the families to show in order, so the family with the most recent DateReceived is on top.

Does anyone know how I can achieve this?

Thank you for anyone who suggested adding a desc in, I have in fact got this, but it is not want I am after.

In the above table, I would want all the results returned as:

ID  |DateUploaded|FamilyCode|
4   |01/08/2012  |FC0002    |
6   |01/07/2012  |FC0002    |
5   |01/01/2012  |FC0002    |
7   |01/01/2012  |FC0002    |
8   |01/08/2012  |FC0003    |
9   |01/07/2012  |FC0003    |
3   |01/01/2012  |FC0001    |
2   |01/01/2011  |FC0001    | 
1   |01/01/2010  |FC0001    |

So the results are grouped into families, with the family with the newest dateuploaded at the top, and the results within the group ordered by date.

Upvotes: 1

Views: 116

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

I suspect that you want all the families together, based on the most recent date. To do this, you need to add in the maximum date uploaded for each family. Here is one method:

select FamilyCode, DateUploaded, . . .
from (select t.*,
             max(DateUploaded) over (parition by FamilyCode) as maxDateUploaded
      from t
     ) t
order by maxDateUploaded, FamilyCode

Note that the order by still includes the FamilyCode. This is important, because multiple families may share the same maximum date, and you still want the families grouped together.

Upvotes: 2

SRIRAM
SRIRAM

Reputation: 1888

use order by desc

order by dateuploaded desc,familycode

Upvotes: 0

AnandPhadke
AnandPhadke

Reputation: 13486

ORDER BY FamilyCode, DateUploaded DESC

Upvotes: 0

Related Questions