GROUP BY and DISTINCT

[Echilon]

I have a few tables and I need to select multiple fields from each. This query works but it doesn't class rows as unique, since if multiple translationids exist, there are multiple rows with the same translationid. I need a single row for each translationid.

SELECT DISTINCT elements.translationid, translations.language, translations.translation, translationcomments.comment
FROM elements
INNER JOIN translations ON translations.translationid = elements.translationid
LEFT JOIN translationcomments ON translationcomments.translationid = translations.translationid

ORDER BY elements.id

If I add a group by clause for "translationid", for a reason I don't understand only a quarter or so of the results are returned. Is there a way I can select just the unique translationids?

 

[r937]

Is there a way I can select just the unique translationids?

certainly

SELECT DISTINCT translationid FROM elements

okay, sorry, i know you wanted something more

the point here is that if you want one row per translationid, along with other stuff in the SELECT clause, then all the other stuff in the SELECT clause has to be aggregate functions

SELECT [red]elements.translationid[/red]
     , [blue]MAX(translations.language)[/blue]
     , [blue]MIN(translations.translation)[/blue]
     , [blue]MAX(translationcomments.comment)[/blue]
  FROM elements 
INNER 
  JOIN translations 
    ON translations.translationid = elements.translationid 
LEFT OUTER
  JOIN translationcomments 
    ON translationcomments.translationid = translations.translationid 
GROUP 
    BY [red]elements.translationid[/red]

but that probably isn't going to help you much either

perhaps you could give some more information about what other stuff you want along with the unique translationids?

r937.com | rudy.ca