SQL Order By and "Not-So-Much Group"

Question

Hello,

Lets say I have a table:
--------------------------------------
|  ID  |  DATE  |  GROUP  |  RESULT  |
--------------------------------------
| 1    | 01/06  | Group1  | 12345    |
| 2    | 01/05  | Group2  | 54321    |
| 3    | 01/04  | Group1  | 11111    |
--------------------------------------

I want to order the result by the most recent date at the top but group the "group" column together, but still have distinct entries. The result that I want would be:

1 | 01/06 | Group1 | 12345
3 | 01/04 | Group1 | 11111
2 | 01/05 | Group2 | 54321

What would be a query to get that result?

thank you!

EDIT:

I'm using MSSQL. I'll look into translating the oracle query into MS SQL and report my results.

EDIT

SQL Server 2000, so OVER/PARTITION is not supported =[

Thank you!

Answer 1

use an order by clause with two params:

...order by group, date desc

this assumes that your date column does hold dates and not varchars

Answer 2

select * from your_table order by [date], [group]

Answer 3

You should specify what RDBMS you are using. This answer is for Oracle, may not work in other systems.

SELECT * FROM table
ORDER BY MAX(date) OVER (PARTITION BY group) DESC, group, date DESC

Answer 4

declare @table table (
 ID int not null,
 [DATE] smalldatetime not null,
 [GROUP] varchar(10) not null,
 [RESULT] varchar(10) not null
)

insert @table values (1, '2009-01-06', 'Group1', '12345')
insert @table values (2, '2009-01-05', 'Group2', '12345')
insert @table values (3, '2009-01-04', 'Group1', '12345')


select t.*
from @table t
inner join (
 select 
  max([date]) as [order-date],
  [GROUP]
 from @table orderer
 group by
  [GROUP]
) x
 on t.[GROUP] = x.[GROUP]
order by
 x.[order-date] desc,
 t.[GROUP],
 t.[DATE] desc

Answer 5

SELECT table2.myID,
 table2.mydate, 
 table2.mygroup, 
 table2.myresult
FROM (SELECT DISTINCT mygroup FROM testtable as table1) as grouptable
JOIN testtable as table2
 ON grouptable.mygroup = table2.mygroup
ORDER BY grouptable.mygroup,table2.mydate

SORRY, could NOT bring myself to use columns that were reserved names, rename the columns to make it work :)

this is MUCH simpler than the accepted answer btw.