ORDER BY select 语句使用 UNION &清楚的
我正在使用以下查询来填充值的下拉列表.
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
我想对结果进行 A-Z 排序.我尝试了以下方法:
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
但是我收到一个错误:
关键字联合"附近的语法不正确.
Incorrect syntax near the keyword 'Union'.
此外，此查询正在提取Blank or NULL"值并在下拉列表顶部显示一个空格.如果可能的话，我想隐藏它.不显示任何空值?
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?
推荐答案
感谢大家的回复，它们让我对在哪里查找问题有了很多见解.添加以下内容的原始查询获得了正确的结果.
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.
工作查询:
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
选择城市"始终位于下拉列表的顶部.感谢@scsimon 在我的另一篇文章中对此的贡献.
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
相关文章