Solved

SELECT DISTINCT WITH ORDER (SQL Server)

Posted on 2008-10-24
4
2,272 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

730 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