Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Sorting UNION query

Discussion in 'Information Technology' started by miguel.guerreiro@talecris.com, Jul 28, 2006.

  1. miguel.guerreiro@talecris.com

    Guest

    Hi folks,

    I wanted to create a query that returns a list of company names sorted
    alphabetically, and then I just add 1 line (constant) to the top of the
    list so my results should look like:
    _____________
    MyStringHere
    Axxxx
    Bxxxxx
    Cxxxx
    Dxxxxx
    _____________

    I created the following query that works and does everything I want,
    except the sorting is screwed up. It ignores the ORDER BY clause and
    doesn't sort anything.
    SELECT DISTINCT 'MyStringHere' FROM tblAddress
    UNION ALL
    (SELECT Company FROM tblAddress ORDER BY Company ASC);

    So, this query actually gives me a result that looks like:
    ____________
    MyStringHere
    Cxxxx
    Axxxx
    Dxxxxx
    Bxxxx

    Any idea on how I can get this UNION to be sorted the way I need it ?


    Thanks.
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Sikh Union Make Special Visit To Houses Of Parliament To Showcase Their Community Work - Breaking News Jul 30, 2016
    Heritage India's Union Sports Ministry To Promote Gatka History of Sikhism Oct 23, 2013
    Hi-Tech Unites the World's Sikhs in Daily Communion Sikh Sikhi Sikhism Jun 29, 2013
    The Holy Union of Anand Karaj New to Sikhism Mar 16, 2013
    General Union with God (BoKSD 6) Hard Talk Dec 4, 2012

  3. Michael H

    Michael H
    Expand Collapse
    Guest

    Hi.

    If I remember correctly, you can only sort on the fields in the first part
    of a Union query. So, reverse the order (i.e. Select Company, and Union
    Select MyStringHere) and your Companies will be sorted.

    However, MyStringHere will sort under "M", so I would suggest changing it to
    something like (MyStringHere), so that not only will it sort to the top of
    the list, but it will also be more distinguishable from the other entries.

    -Michael



    "miguel.guerreiro@talecris.com" wrote:

    > Hi folks,
    >
    > I wanted to create a query that returns a list of company names sorted
    > alphabetically, and then I just add 1 line (constant) to the top of the
    > list so my results should look like:
    > _____________
    > MyStringHere
    > Axxxx
    > Bxxxxx
    > Cxxxx
    > Dxxxxx
    > _____________
    >
    > I created the following query that works and does everything I want,
    > except the sorting is screwed up. It ignores the ORDER BY clause and
    > doesn't sort anything.
    > SELECT DISTINCT 'MyStringHere' FROM tblAddress
    > UNION ALL
    > (SELECT Company FROM tblAddress ORDER BY Company ASC);
    >
    > So, this query actually gives me a result that looks like:
    > ____________
    > MyStringHere
    > Cxxxx
    > Axxxx
    > Dxxxxx
    > Bxxxx
    >
    > Any idea on how I can get this UNION to be sorted the way I need it ?
    >
    >
    > Thanks.
    >
    >
     
  4. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    I Like to make a Duplicate of Oracle's DUAL for these purposes (single row,
    single column)

    CREATE TABLE DUAL (X CHAR(1));
    INSERT INTO DUAL VALUES('X');

    SELECT 'MyString' AS CompanyName, 0 AS SORT FROM DUAL
    UNION ALL
    SELECT Company, 1 AS SORT FROM tblAddress
    ORDER BY 2,1

    HTH
    Pieter

    "Michael H" <MichaelH@discussions.microsoft.com> wrote in message
    news:B5122E74-7D98-49FE-9546-3BAFCAC7D475@microsoft.com...
    > Hi.
    >
    > If I remember correctly, you can only sort on the fields in the first part
    > of a Union query. So, reverse the order (i.e. Select Company, and Union
    > Select MyStringHere) and your Companies will be sorted.
    >
    > However, MyStringHere will sort under "M", so I would suggest changing it
    > to
    > something like (MyStringHere), so that not only will it sort to the top of
    > the list, but it will also be more distinguishable from the other entries.
    >
    > -Michael
    >
    >
    >
    > "miguel.guerreiro@talecris.com" wrote:
    >
    >> Hi folks,
    >>
    >> I wanted to create a query that returns a list of company names sorted
    >> alphabetically, and then I just add 1 line (constant) to the top of the
    >> list so my results should look like:
    >> _____________
    >> MyStringHere
    >> Axxxx
    >> Bxxxxx
    >> Cxxxx
    >> Dxxxxx
    >> _____________
    >>
    >> I created the following query that works and does everything I want,
    >> except the sorting is screwed up. It ignores the ORDER BY clause and
    >> doesn't sort anything.
    >> SELECT DISTINCT 'MyStringHere' FROM tblAddress
    >> UNION ALL
    >> (SELECT Company FROM tblAddress ORDER BY Company ASC);
    >>
    >> So, this query actually gives me a result that looks like:
    >> ____________
    >> MyStringHere
    >> Cxxxx
    >> Axxxx
    >> Dxxxxx
    >> Bxxxx
    >>
    >> Any idea on how I can get this UNION to be sorted the way I need it ?
    >>
    >>
    >> Thanks.
    >>
    >>
     
  5. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    I Like to make a Duplicate of Oracle's DUAL for these purposes (single row,
    single column)

    CREATE TABLE DUAL (X CHAR(1));
    INSERT INTO DUAL VALUES('X');

    SELECT 'MyString' AS CompanyName, 0 AS SORT FROM DUAL
    UNION ALL
    SELECT Company, 1 AS SORT FROM tblAddress
    ORDER BY 2,1

    HTH
    Pieter

    "Michael H" <MichaelH@discussions.microsoft.com> wrote in message
    news:B5122E74-7D98-49FE-9546-3BAFCAC7D475@microsoft.com...
    > Hi.
    >
    > If I remember correctly, you can only sort on the fields in the first part
    > of a Union query. So, reverse the order (i.e. Select Company, and Union
    > Select MyStringHere) and your Companies will be sorted.
    >
    > However, MyStringHere will sort under "M", so I would suggest changing it
    > to
    > something like (MyStringHere), so that not only will it sort to the top of
    > the list, but it will also be more distinguishable from the other entries.
    >
    > -Michael
    >
    >
    >
    > "miguel.guerreiro@talecris.com" wrote:
    >
    >> Hi folks,
    >>
    >> I wanted to create a query that returns a list of company names sorted
    >> alphabetically, and then I just add 1 line (constant) to the top of the
    >> list so my results should look like:
    >> _____________
    >> MyStringHere
    >> Axxxx
    >> Bxxxxx
    >> Cxxxx
    >> Dxxxxx
    >> _____________
    >>
    >> I created the following query that works and does everything I want,
    >> except the sorting is screwed up. It ignores the ORDER BY clause and
    >> doesn't sort anything.
    >> SELECT DISTINCT 'MyStringHere' FROM tblAddress
    >> UNION ALL
    >> (SELECT Company FROM tblAddress ORDER BY Company ASC);
    >>
    >> So, this query actually gives me a result that looks like:
    >> ____________
    >> MyStringHere
    >> Cxxxx
    >> Axxxx
    >> Dxxxxx
    >> Bxxxx
    >>
    >> Any idea on how I can get this UNION to be sorted the way I need it ?
    >>
    >>
    >> Thanks.
    >>
    >>




    --
    ----------------------------------------
    I am using the free version of SPAMfighter for private users.
    It has removed 4367 spam emails to date.
    Paying users do not have this message in their emails.
    Get the free SPAMfighter here: http://www.spamfighter.com/len
     
  6. M. Guerreiro

    M. Guerreiro
    Expand Collapse
    Guest

    Thanks for your help guys.
    Michael: My query already does what you propose, so your solution
    didn't really solve my problem. I don't want the constant string to be
    sorted as part of the rest of the list of query results.

    Pieter: I like your solution, and would implement it as a last resort
    because it requires the creation of tables.

    I was able to find a weird but ellegant solution that sorts without you
    telling it to. It's mind blowing, so I'll be sharing it here for the
    benefit of all. The combination of a UNION statement with a UNION ALL
    does the trick. The proponent of this solution knew about the internal
    behavior of the SQL engine. Check out the fact that I never instruct
    the query to sort, yet it does, because of the WHERE clause in the
    UNION. Also, instead of using a DISTINCT command I used TOP 1 which is
    much faster since it requires no internal sorting.

    SELECT TOP 1 'MyCompanyName' FROM tblAddress
    UNION ALL
    (SELECT Company1 FROM tblAddress WHERE Company1>=''
    UNION SELECT Company1 FROM tblAddress WHERE Company1>='' );

    Thanks again guys.
     
  7. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    The DUAL table is a good approach though, as it will work on any SQL-89
    compatible plattform & you'd only have to make it once per application ;-)

    Pieter

    "M. Guerreiro" <miguel.guerreiro@talecris.com> wrote in message
    news:1153918447.061854.92950@75g2000cwc.googlegroups.com...
    > Thanks for your help guys.
    > Michael: My query already does what you propose, so your solution
    > didn't really solve my problem. I don't want the constant string to be
    > sorted as part of the rest of the list of query results.
    >
    > Pieter: I like your solution, and would implement it as a last resort
    > because it requires the creation of tables.
    >
    > I was able to find a weird but ellegant solution that sorts without you
    > telling it to. It's mind blowing, so I'll be sharing it here for the
    > benefit of all. The combination of a UNION statement with a UNION ALL
    > does the trick. The proponent of this solution knew about the internal
    > behavior of the SQL engine. Check out the fact that I never instruct
    > the query to sort, yet it does, because of the WHERE clause in the
    > UNION. Also, instead of using a DISTINCT command I used TOP 1 which is
    > much faster since it requires no internal sorting.
    >
    > SELECT TOP 1 'MyCompanyName' FROM tblAddress
    > UNION ALL
    > (SELECT Company1 FROM tblAddress WHERE Company1>=''
    > UNION SELECT Company1 FROM tblAddress WHERE Company1>='' );
    >
    > Thanks again guys.
    >




    --
    ----------------------------------------
    I am using the free version of SPAMfighter for private users.
    It has removed 4367 spam emails to date.
    Paying users do not have this message in their emails.
    Get the free SPAMfighter here: http://www.spamfighter.com/len
     

Share This Page