SQL getting TOP 1 result for each name?

[VRao]

Below are my sample records in a SQL table.

FName ........ Points.............. Date
Toyota ............150............ 05/12/2005
Honda ............115 ............10/10/2003
Mitsubishi.......120............ 06/06/2005
Toyota.............130.............01/09/2005
Honda ............135 ............11/07/2003
Mitsubishi ......110.............16/06/2005
Toyota ............160.............25/11/2005
Honda .............125............12/12/2003
Mitsubishi........110 ...........26/02/2005

I want to get the result as below.

Toyota ...........160 ..... 25/11/2005
Honda ........... 135 ..... 11/07/2003
Mitsubishi...... 120 ...... 06/06/2005

How can i get the TOP 1 records for each FName column?

Please help. Thx.

[thethirdheat]

Use the rank() function (Oracle only)

SELECT *
FROM TABLE
WHERE (SELECT RANK() OVER POINTS FROM TABLE) > 3

In this case, this query should return the 3 rows with the greatest value for POINTS. My memory of the correct syntax for RANK() is foggy, but that'll get the job done. You'll need to use RANK() in an inner query to get the first n rows, otherwise it'll return all rows order by rank.

Or I think the Max() function (oracle only) should get 1 row with the maximum value for the specified column. For example, you want the row with the greatest value for the POINTS column:

SELECT FNAME
FROM TABLE
WHERE MAX(POINTS)

I'm not 100% on my syntax, but the idea should work

[n_arent]

This is how I would do it... someone may do it differently.

select fname, max(points) as max_points
into #temptable
from [table name]
go

select a.*
from [table name] a, #temptable b
where a.fname = b.fname
and a.points = b.max_points
go

This is assuming that points are unique within each fname group and that you desire only one record returned for each fname. If you have records that are tied for first, then you will have to dedupe the results... but that's another question. Maybe someone has a solution that can accomplish this in one statement instead of two.