0

I'm using the following query to populate a dropdown field with results from a business directory table.

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

I'd like to keep my 'Select a City' at the top of the list and not have it ordered alphabetically but keep everything else the same.

Is this possible?

UserSN
  • 953
  • 1
  • 11
  • 33
  • 2
    Side note, please consider accepting correct answers on your previous questions.... it encourages others to help you out. Cheers. http://stackoverflow.com/questions/39626354/order-by-select-statement-using-union-distinct http://stackoverflow.com/questions/39624368/t-sql-query-to-search-table-field-for-keywords http://stackoverflow.com/questions/39542201/sql-split-string-as-key-identity-value etc... – S3S Sep 22 '16 at 18:44
  • Hello @scsimon yes, i'm going to answer those topics now. I ended up using the original query as there was a bug on the plug-in side so the input was highly appreciated but at the end of the day I ended up sticking with the original query and i cant answer my own questions the same day. – UserSN Sep 22 '16 at 18:52
  • sounds good Alex. – S3S Sep 22 '16 at 18:53

2 Answers2

1
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
S3S
  • 24,809
  • 5
  • 26
  • 45
  • that gives me error message: ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator. – UserSN Sep 22 '16 at 18:47
  • 1
    @AlexP wrapped it in a CTE to make it easier – S3S Sep 22 '16 at 18:51
  • Thank you @scsimon this works! I'm going to read up about the concept of "wrapping" i see it used often and cte. I'm still new to tsql takes a bit longer to understand whats making things work and not. – UserSN Sep 22 '16 at 18:57
  • 1
    @AlexP just be REAL careful using CTE with update, truncate, and delete statements. They can be real powerful and can be used sort of like temp tables in VIEWs and other places. – S3S Sep 22 '16 at 19:00
  • thanks for the warning I'll read up about CTE to understand it better. – UserSN Sep 22 '16 at 19:03
1

Another way to approach this would be:

-- sample data
DECLARE @BND_Listing TABLE (City varchar(100) UNIQUE);
INSERT @BND_Listing VALUES ('Chicago'),('New York'),('LA');

-- solution
SELECT City, Value
FROM
(
  SELECT 0 as oGroup, 'Select a City' as City, 'All' as Value
  UNION ALL
  SELECT DISTINCT 1, City, City FROM @BND_Listing
  WHERE City IS NOT NULL AND City <> ''
) prep
ORDER BY oGroup;

Provided that you have an index on City (I created one using the UNIQUE constraint in my DDL) you will get your results without a sort in the execution plan.

Alan Burstein
  • 7,770
  • 1
  • 15
  • 18
  • thanks for your example. The previous answer got it working for me. I'll have to read a little about index's and what they could be used for. I'm currently looking at something about index's for States/Countries because as it stands now the query i'm using from scsimon is pulling data as entered into my BND_Listing table. But i'm storing state abbreviations same for country and i'd like to display those as the full spelled out state/country. Thanks for your answer! – UserSN Sep 22 '16 at 19:26