?
Solved

SELECT DISTINCT WITH ORDER (SQL Server)

Posted on 2008-10-24
4
Medium Priority
?
2,306 Views
Last Modified: 2013-11-11
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

0
Comment
Question by:mars
  • 2
  • 2
4 Comments
 
LVL 2

Assisted Solution

by:carter-greenan
carter-greenan earned 100 total points
ID: 22800214
Hi there, give this a try. It is using a CTE to get around the issue

WITH names (Name, Priority) AS
(
    SELECT DISTINCT TOP (200) Name, Priority
    FROM MyTable
    ORDER BY Name
)
SELECT *
FROM Names
ORDER BY Priority
WHERE DirectReports>= 2 ;
GO
0
 
LVL 2

Expert Comment

by:carter-greenan
ID: 22800220
Sorry, please ignore the WHERE Clause, it was a copy/paste error!
0
 

Author Comment

by:mars
ID: 22800312
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 ?







0
 

Accepted Solution

by:
mars earned 0 total points
ID: 22802963
I found a solution that is :

SELECT top 10 Name, max(Priority) as PR
FROM MyTable
where .....
group by Name
order by PR desc

It works and return only unique NAME field value.

But I DO NOT KNOW HOW to code this SQL statement in LINQ statement ?
Any ideas ?
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month9 days, 1 hour left to enroll

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question