Solved

SELECT DISTINCT WITH ORDER (SQL Server)

Posted on 2008-10-24
4
2,263 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
Comment Utility
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
Comment Utility
Sorry, please ignore the WHERE Clause, it was a copy/paste error!
0
 

Author Comment

by:mars
Comment Utility
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
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
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…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

772 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

10 Experts available now in Live!

Get 1:1 Help Now