Max Row from a group 1

[IAMINFO]

Hello everyone,
The following sql

Select Severity,Offender,Descr
from chargeview
where offender = 16744 and eclock is null

produces the following result set

severity offender descr
99300 16744 vop
14200 16744 agg asslt
99904 16744 187
53232 16744 bw

I need the result set to only show the row with the highest severity level. I am not sure how to write the subquery if one is needed , any guidance or assistance would be most appreciated.
Thank you for taking the time to read this post and helping.

 

[bborissov]

Select Severity,Offender,Descr
from chargeview
INNER JOIN (SELECT MAX(Severity) AS Severity
                   from chargeview
            where offender = 16744 and eclock is null) Tbl1
ON chargeview.Severity = Tbl1.Severity
where chargeview.offender = 16744 and 
      chargeview.eclock is null

If you use 2005 or 2008 you could use ROW_NUMBER() OVER (...).


Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[IAMINFO]

bborissov's thank you so very much , the code worked perfectly
I had researched the issue with no luck, again thank you

 

[markros]

See these links to learn alternative solutions of this common problem

Including an Aggregated Column's Related Values
Including an Aggregated Column's Related Values - Part 2


PluralSight Learning Library