10

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?

bakerjr
  • 397
  • 2
  • 5
  • 16

3 Answers3

21

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."

Lasse V. Karlsen
  • 380,855
  • 102
  • 628
  • 825
  • +1 Good answer. Did you leave the group by off for the content_name? – James Westgate Apr 27 '10 at 11:53
  • I also use this method but I always feel dirty doing it. Is MIN and MAX really the right thing to do here? What do other databases use such as mySQL and Oracle that let you select a single column for DISTINCT keyword? Are they doing a MIN/MAX behind the scenes? – emalamisura Oct 15 '12 at 16:52
  • Well I guess Oracle doesn't let you do that but I would like to know how mySQL works in that particular fashion... – emalamisura Oct 15 '12 at 17:02
  • I don't know, but frankly that is a question on its own, so you should post it if you really want to know. It involves more space than available in a comment. – Lasse V. Karlsen Jun 30 '14 at 10:00
5
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
Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • 1
    This way is far better than doing MINs in other answers because MIN are calculated independently for every column so you would end up with rows that actually do not exists in db – sparrow Apr 11 '17 at 09:00
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
Salil
  • 46,566
  • 21
  • 122
  • 156