union all and order by help!
-
i need to select country_id =26 and then the rest i want them ordered by name.
if i put order by country_name, the country_id 26 isn't displayed as the first one.
is there anyway to apply the order by only to the second select not the whole?
SELECT *
FROM tblCountry
WHERE Country_Id = 26
UNION ALL
SELECT *
FROM tblCountry
WHERE Country_Id <> 26
--order by country_Name
-
Answer is no but you do not need a UNION use CASE instead
SELECT *
FROM tblCountry
ORDER BY CASE WHEN Country_Id = 26 THEN 1 ELSE 2 END, Country_Name
Far away is close at hand in the images of elsewhere.
Anon. -
Yes there is! But I agree with you that a CASE is more readable.
SELECT f.Country_Name
FROM (
SELECT Country_Name
FROM tblCountry
WHERE Country_ID = 26
UNION ALL
SELECT TOP 100 PERCENT Country_Name
FROM tblCountry
WHERE Country_ID <> 26
ORDER BY Country_Name
) f
N 56°04'39.16"
E 12°55'05.25" -
I stand corrected
Far away is close at hand in the images of elsewhere.
Anon. -
Why wouldn't David's version work? Nulls? Duplicates?
SELECT Country_Name
FROM tblCountry
WHERE NOT Country_Id IS NULL
ORDER BY
CASE Country_Id WHEN 26 THEN 1 ELSE 2 END,
Country_Name
SELECT t.Country_Name
FROM (
SELECT DISTINCT Country_Id, Country_Name
FROM tblCountry
WHERE NOT Country_Id IS NULL
) t
ORDER BY
CASE t.Country_Id WHEN 26 THEN 1 ELSE 2 END,
t.Country_Name
Randy
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply