Get the maximum value of rows from database table

Question

I need to write a SQL query which will get me those rows from the table which have the max value of files.

The database table is as follows:

> ID  status dept  files
> 
> > 1   1 23 1256637314
> > 
> > 1   1 39 1256642968
> > 
> > 2   0 85 1256551419
> > 
> > 2   1 90 1256642968
> > 
> > 2   1 93 1256810937
> > 
> > 3   0 20 1256642968
> > 
> > 5   1 342 1256810937

Now from this table i want to get those rows which have the maximum files grouping by ID:

> ID  status dept  files
>
> 1    1   39  1256642968
>
> 2    1   93  1256810937
>
> 3    0   20  1256642968
>
> 5    1   342 1256810937

Any suggestions??

Thanks.

i tried this but this is not right

Select  ID, Status, dept,files
from SLAStat
where files in (Select Max(files) from SLAStat group by ID)




2   1 90 1256642968
1   1 39 1256642968
3   0 20 1256642968
5   1 342 1256810937
2   1 93 1256810937

Answer 1

SELECT DISTINCT a.ID, a.Status, a.Dept, a.Files
FROM table a
INNER JOIN (
SELECT ID, MAX(files) AS Files
FROM table
GROUP BY ID) b ON a.ID = b.ID AND a.Files = b.Files

Answer 2

SELECT MAX(files) as max_files, id, status, dept
FROM table
GROUP BY id

Answer 3

I may be over-simplifying the problem, but:

select top 5 * from SLAStat order by files desc;

Answer 4

Replace @t1 by your table:

With idT as (
    select ID
    from @t1
    group by id
) 
select applyT.* 
from idT p
CROSS APPLY (
    select top 1 * from @t1 where ID=p.ID order by files desc
) as applyT