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.
3 Solutions collect form web for “Ordering Ordered Groups”
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.
ORDER BY FamilyCode, DateUploaded DESC
use order by desc
order by dateuploaded desc,familycode