Solved

SELECT DISTINCT WITH ORDER (SQL Server)

Posted on 2008-10-24
4
2,265 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 25 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

864 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now