SQLTeam.com | Weblogs | Forums

"Select All" on top of the list

sql2008

#1

Hi,
I have the following query:

SELECT 'Select All' as col1
UNION
SELECT DISTINCT col1
FROM table1
ORDER BY col

I get the results as follows:
1
2
3
Select All

I need to have the result as follows:
Select All,
1
2
3

How can I accomplish this?

Thank you.


#2

This was the solution for me:

SELECT 1 as SortOrder, 'Select All' as col1
UNION
SELECT DISTINCT 2 as SortOrder, col1
FROM table1
ORDER BY SortOrder, col1


#3

Suggest using UNION ALL as it is faster (provided that you don't need a de-DUP of the results)

I would also want to avoid include the SortOrder in the resultset, so my code would be

SELECT Col1, Col2, ...
FROM
(
    SELECT 1 as SortOrder, 'Select All' as col1, col2, ...
    UNION ALL
    SELECT DISTINCT 2 as SortOrder, col1, col2, ...
    FROM table1
) AS X
ORDER BY SortOrder, col1

the DISTINCT also worries me, it would be better to code out of the process the duplicates, rather than have SQL select them all ... and then sort them ... and then remove the DUPs. For example, if this is a picklist and it is intended to show only unique values used in [Col1], then perhaps maintain a cached list in a separate table. If this query is run often, e.g. every time the picklist needs to be displayed, and it is finding a handful of unique values for [Col1] from thousands, or millions, of rows it will scale very badly.