Help with a simple query please

Question

This could be very simple but I can't get around this. Need to all the word 'ALL' to a result set that I will show on drop down list parameter in a report. I need to all the word 'ALL' that's all good but when I sort it the 'ALL' ends up somewhere deep in the results. the 'ALL' should be at the top of the result set plus the result set should be sorted alphabetically.

Thanks for your help.

Manny

Answer 1

You may add a conditional sort, e.g.

 

select 'ALL' as Product

UNION ALL

select Product from Products

ORDER BY CASE when Product = 'ALL' then 1 else 2 END, -- this is conditional sort

Product

 

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

Depending on the complexity of your situation, can you just use "<All>" then it will sort to the top, if your data is simple enough

DECLARE @Test AS TABLE
(Word varchar(20))

INSERT INTO @Test VALUES ('<ALL>')
INSERT INTO @Test VALUES ('Anna')
INSERT INTO @Test VALUES ('Bananna')

SELECT * FROM @Test
ORDER BY Word

---

If there was a problem - Yo, I'll solve it

Answer 3

>> Need to add the word 'ALL' to a result set that I will show on drop down list parameter in a report. << 

Where is the DDL? The sample data? Drop-down list? Report? Sorting? This is an SQL forum and those things exists in the front end. They have no place in SQL because we are a tiered architecture. 

When we wrote in COBOL, FORTRAN, Assembly languages or even a 4GL, the data and the display were in a monolithic block on code. You are about 50 years behind. Today, the front end would get a result set and handle all of that stuff. 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 4

Hi Manny

 

The below piece of code should work

I have put in the same data for your convinience...

 

drop table #t
create table #t
(
	product varchar(10)
)
insert into #t values ('ALL')
insert into #t values ('ABCD')
insert into #t values ('AAAA')
insert into #t values ('XYZ')
insert into #t values ('BFRG')

select product
from #t
order by case WHEN product = 'ALL' Then 1 else 2 end,product

Answer 5

Jez man relax..lol