mars
asked on
SELECT DISTINCT WITH ORDER (SQL Server)
Hi,
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 !
Thank you
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 !
Thank you
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sorry, please ignore the WHERE Clause, it was a copy/paste error!
ASKER
Thank you but i always get duplicated NAME values.
For instance with your SQL, here is an excerpt of what i got as result set :
NAME - PRIORITY
wanadoo - 10
wanadoo - 8
I only need to have only ONE wanadoo value.
Do you think there is an SQL solution, maybe LINQ statements will be a greater help ?
For instance with your SQL, here is an excerpt of what i got as result set :
NAME - PRIORITY
wanadoo - 10
wanadoo - 8
I only need to have only ONE wanadoo value.
Do you think there is an SQL solution, maybe LINQ statements will be a greater help ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.