CODEWITHSUNDEEP
sqlsql-serversql-server-2008
James
James

Reputation: 2542

Select distinct value from one column, using another for ordering

I have a table with two columns, Name (nvarchar(256)) and Score (int between 0 and 100). Each name can have more than one score. I know it should be simple, but I can't work out how to get one table containing each name only once, and the top score for that name. Can anyone help?

Upvotes: 0

Views: 91

Answers (4)

Alessandro
Alessandro

Reputation: 1453

Try this

SELECT Name, max(Score) as Score
FROM table
GROUP BY Name
ORDER BY Score desc

I suggest you to give a look at W3Schools SQL Tutorial. It explains the basic things and the basic functions (on the right SQL Basic and SQL Functions), if you give a look at these lessons you'll be able to do lot of things by yourself, it takes about 20 minutes of reading + the time you should reserve for trying ;)

Upvotes: 0

Vlad G.
Vlad G.

Reputation: 2147

This should do it:

SELECT Name, MAX(Score)
FROM t
GROUP BY Name

Upvotes: 0

Adrian Wragg
Adrian Wragg

Reputation: 7411

Something like:

SELECT Name, max(score)
  FROM Table
  GROUP BY Name

should do what you're after.

Upvotes: 1

Ian Preston
Ian Preston

Reputation: 39606

The simplest approach would be:

select [Name], max([Score])
from t1
group by [Name]

Upvotes: 1

Related Questions