I'm using the following query to populate a dropdown list of values.
select 'Select a City' as City, 'All' as Value
UNION ALL
select distinct City, City as Value from BND_Listing
I'd like to sort A-Z the results. I've tried the following:
select 'Select a City' as City, 'All' as Value
UNION ALL
select distinct City, City as Value from BND_Listing
ORDER BY City ASC
But am getting an error:
Incorrect syntax near the keyword 'Union'.
Additionally this query is pulling "Blank or NULL" values and displaying a blank space at the top of the drop-down. I'd like to hide that if possible. Not display any null value?
You want to add a row to your result, which is always on top and carries a NULL as ID?
Try this:
SELECT *
FROM
(
SELECT NULL AS col1,'select an object' AS col2,0 AS SortInx
UNION ALL
SELECT TOP 10 object_id,name,ROW_NUMBER() OVER(ORDER BY name)
FROM sys.objects
) AS Sortable
ORDER BY SortInx
Short explanation: ROW_NUMBER() start with 1, so the first row gets 0 as sort index. The numbers from 1 to x represent the sorted name's order.
The outer SELECT will sort the result-set again making sure, that 0 is in front and 1 to x following...
I agree with most of the comments here where the best approach is to actually have the "Select a Value" row added in the application itself. It's probably best to have the database only delivering "actual" data to your application and handle things like that in the code.
I'm also not sure what this project is for, but if you have access, I would strongly recommend creating views and/or stored procedures at the database level to abstract any database schema and logic changes from your application.
Just out of curiosity, why are you selecting the same field twice with different aliases? I'm assuming you're setting the display value and the actual value in a simple HTML dropdown, but in this case, the values are the same, so you could only have one field in your result set and reference that value twice in the application. Doing this would solve the problem of your original question, as well as simplify your query (although a query this simple is going to have negligible cost anyway) to look like this:
SELECT DISTINCT City
FROM BND_Listing (NOLOCK)
WHERE City IS NOT NULL
ORDER BY 1 ASC
Depending on the data, DB config, etc, you may need to account for empty strings and/or leading/trailing spaces with something like this:
SELECT DISTINCT LTRIM(RTRIM(City)) AS City
FROM BND_Listing (NOLOCK)
WHERE LTRIM(RTRIM(City)) <> ''
AND City IS NOT NULL
ORDER BY 1
Sorry...I know that may have been a little overkill, but you said you were new to SQL, so I thought I'd just share that in case your NULL results were actually empty strings.
Thank you everyone for the responses it gave me a lot of insight on where to look for my problem. The original query with the addition of the below achieved the proper result.
working query:
select 'Select a City' as City, 'All' as Value
UNION ALL
select distinct City, City as Value from BND_Listing
where isnull(City,'') <> ''
Order by City ASC
with 'Select a City' always at the top of the dropdown. Credit to @scsimon on my other post for this.
with cte as(
select 'Select a City' as City, 'All' as Value
UNION ALL
select distinct City, City as Value from BND_Listing
where isnull(City,'') <> '')
select * from cte Order by case when City = 'Select a City' then 1 else 2 end, City ASC
