SELECT "TOP" FROM...

Database

SELECT "TOP" FROM...

My table looks like this:

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.

I appreciate any help. Thank you.
3 REPLIES

Re: SELECT "TOP" FROM...

I just tried this an it seems to be working:

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
Enthusiast

Re: SELECT "TOP" FROM...

Try this one...

SELECT
KeyID
,Language
,ROW_NUMBER() OVER (PARTITION BY KeyID ORDER BY Language DESC) as TheTop
FROM tblMyTable
qualify TheTop = 1

Re: SELECT "TOP" FROM...

Why not simply using :

SELECT
KeyId,
max("Language")
from tblMyTable
group by 1

(Note : Language is a keyword)