SELECT DISTINCT WITH ORDER (SQL Server)
Posted on 2008-10-24
I have a table with 2 columns
- NAME nvarchar
- PRIORITY int
I need to retrieve the 200 first distinct records from this table, i write the folowing query but it failed with SQL Server:
select distinct TOP 200 NAME from MyTable order by PRIORITY DESC
The error message says that i must insert the column Priority in the selection fields like this :
select distinct TOP 200 NAME, PRIORITY from MyTable order by PRIORITY DESC
This SQL statement works BUT it does not what i want. The result set contains duplicated NAME field values because the PRIORITY for these NAMES are differents.
What do i have to do to get a real distinct on my NAME columns and ordered by PRIORITY column ?
In final step, i need to code this SQL in LINQ statement.
It's very funny because the equivalent query works very well in MySQL, why MS SQL Server will be so different !