How to select distinct in SQl Server 2008 but for only one field out of many?

Question

I have a query:

SELECT Content.content_name, Language2.Name, Language2.language_id, 
Content.id, Content.content_description, 
FROM Language AS Language2 
LEFT JOIN contents AS Content ON (Language2.language_id = Content.language_id) 

How do I select only the distinct content_name?

Answer 1

You mean something like following

SELECT Content.content_name, 
FROM Language AS Language2 
LEFT JOIN contents AS Content ON (Language2.language_id = Content.language_id)
Group by Content.content_name 

Answer 2

You do this:

SELECT DISTINCT Content.content_name
FROM Language AS Language2 
LEFT JOIN contents AS Content ON (Language2.language_id = Content.language_id)

So why does this not answer your question?

Let's consider the following data (just the first two columns):

content_name      Name
 XXXXX            1234
 XXXXX            5678

SELECT DISTINCT implies you only want one row, but what do you want for Name?

What you need to do is rewrite the code to use GROUP BY and pick the appropriate aggregate function for the other columns:

SELECT
    Content.content_name,
    MIN(Language2.Name) AS Name,
    MIN(Language2.language_id) AS language_id, 
    MIN(Content.id) AS id,
    MIN(Content.content_description) AS content_description, 
FROM
    Language AS Language2 
    LEFT JOIN contents AS Content
        ON (Language2.language_id = Content.language_id) 
GROUP BY
    Content.content_name

Now, likely this does not produce what you want either, but one thing is for certain, you can not trick the database engine to just "pick one of the rows to return, I don't care which one."

Answer 3

WITH    q AS
        (
        SELECT  Content.content_name, Language2.Name, Language2.language_id, Content.id, Content.content_description, ROW_NUMBER() OVER (PARTITION BY content_name ORDER BY language_id) AS rn
        FROM    Language Language2
        LEFT JOIN
                Contents AS Content
        ON      Language2.language_id = Content.language_id
        )
SELECT  *
FROM    q
WHERE   rn = 1