KeyID | Language ----------------- 1 | English 2 | English 2 | French 3 | English 3 | French
I want to write a query that will return "French" (if a KeyID has it) otherwise it'll return "English".
Here's what I have so far:
SELECT KeyID,Language FROM ( SELECT KeyID,Language,1 AS Presidence FROM tblMyTable WHERE Language='French' UNION SELECT KeyID,Language,2 AS Presidence FROM tblMyTable WHERE Language='English' ) AS tmp ORDER BY Presidence
In MS SQL Server or something, I could using "TOP", but Teradata-SQL doesn't have that. I've tryed the RANK() function, but can't seem to get it working--maybe it's not even the right function for the job here.
SELECT tmp3.KeyID , COALESCE(tmp4.Language,tmp3.Language) AS Language2 FROM ( SELECT KeyID,Language FROM tblMyTable WHERE Language='English' ) AS tmp3 LEFT JOIN ( SELECT KeyID,Language FROM tblMyTable WHERE Language='French' ) AS tmp4 ON tmp3.KeyID = tmp4.KeyID