locked
Help with a simple query please RRS feed

  • 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

    Tuesday, July 19, 2011 3:28 PM

Answers

  • 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
    • Proposed as answer by HunchbackMVP Tuesday, July 19, 2011 5:19 PM
    • Marked as answer by Alex Feng (SQL) Tuesday, July 26, 2011 11:27 AM
    Tuesday, July 19, 2011 3:30 PM
  • 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
    

    Tuesday, July 19, 2011 9:31 PM

All replies

  • 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
    • Proposed as answer by HunchbackMVP Tuesday, July 19, 2011 5:19 PM
    • Marked as answer by Alex Feng (SQL) Tuesday, July 26, 2011 11:27 AM
    Tuesday, July 19, 2011 3:30 PM
  • 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
    Tuesday, July 19, 2011 4:03 PM
  • >> 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
    Tuesday, July 19, 2011 9:10 PM
  • 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
    

    Tuesday, July 19, 2011 9:31 PM
  • Jez man relax..lol
    Tuesday, July 19, 2011 10:05 PM