Solved

SELECT DISTINCT WITH ORDER (SQL Server)

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

Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

Question has a verified solution.

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

Flash (http://en.wikipedia.org/wiki/Adobe_Flash) has evolved over the years to where it has become a masterful tool for displaying content screen.  It has excellent layout placement, UI precision as well as rendering capabilities. This, along with t…
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 …
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

695 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