How to select distinct in SQl Server 2008 but for only one field out of many?
I have a query:
SELECT Content.content_name, Language2.Name, Language2.language_id,
Content.id, Content.content_description,
FROM Language AS开发者_JAVA技巧 Language2
LEFT JOIN contents AS Content ON (Language2.language_id = Content.language_id)
How do I select only the distinct content_name?
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."
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
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
精彩评论