stupid question: distinct and sort

[rravenn]

MS SQL 2000.
I have the following query
SELECT DISTINCT TOP 100 Word,Trans,WordID,IsExact,SetN,IsByWord,IsByPart FROM
(
SELECT Name as Word
, dbo.wa_getTranslationList(WordID) as Trans
, WordID
, CASE WHEN Name = @Word THEN 1 ELSE 0 END AS IsExact
, CASE WHEN Name LIKE @Word + '%' THEN 1 ELSE 0 END AS IsByWord
, CASE WHEN Name LIKE '% ' + @Word + '%' THEN 1 ELSE 0 END AS IsByPart
, 1 as SetN
FROm Word WHERE LanguageID = @LanguageID AND Name LIKE '%'+@Word+'%'

UNION

SELECT Name as Word
, dbo.wa_getTranslationList(Word.WordID) as Trans
, Word.WordID
, CASE WHEN Description = @Word THEN 1 ELSE 0 END AS IsExact
, CASE WHEN Description LIKE @Word + '%' THEN 1 ELSE 0 END AS IsByWord
, CASE WHEN Description LIKE '% ' + @Word + '%' THEN 1 ELSE 0 END AS IsByPart
, 2 as SetN
FROm Word
INNEr JOIN Translation ON Word.WordID = Translation.WordID AND Description LIKE '%'+@Word+'%'
WHERE LanguageID = @LanguageID
) tmp
WHERE IsExact = 1 OR IsByWord = 1 OR IsByPart = 1
ORDER BY IsExact DESC,SetN,IsByWord DESC,IsByPart DESC,Word



How do I make DISTINCT work on only three columns, namely WordID, Word and Translation? If the word is matched by both queries in the union it gets in the result set twice because of the sorting columns, and if I get rid of sorting columns I lose sorting

I figured out I can insert counter conditions for the first in the 2nd quert (e.g. matches translation but not the word), but is there a better more standart way to achieve this?

 

[vongrunt]

Ack...

What if there are two or more rows with same WordID, Word and Translation... but different IsExact,SetN,IsByWord,IsByPart ?

There is logical reason why ORDER BY can be done only with DISTINCT columns.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[SQLSister]

If you have 6 fields and only want the distinct records for three of them you must specify which valueyou want fo r the other three fields uless you want to show dups where more than one record exists.

select field1, field2, max(field3), min(field4) from table1 group by field1, field2

Of course in your case you would substitute the whole derived table for table1 and then choose which values you want to display for the other fields. YOu can use max(), Min() , case to figure out which value to display.

If you want to see all the values but only on one record, you will need to concatentate them together into one field. There are FAQS which will show you how to do this.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief