ORDER BY select 语句使用 UNION &清楚的 [英] ORDER BY select statement using UNION & DISTINCT

问题描述

我正在使用以下查询来填充值的下拉列表.

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

这篇关于ORDER BY select 语句使用 UNION &清楚的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！