order by with Union

  • Hi,

    I have to generate an extract file that has header, footer and the bulk of the data in the middle. Something like this

    CREATE TABLE tblContent (content varchar(2))

    INSERT INTO tblContent (content) VALUES ('a')

    INSERT INTO tblContent (content) VALUES ('c')

    INSERT INTO tblContent (content) VALUES ('b')

    INSERT INTO tblContent (content) VALUES ('e')

    INSERT INTO tblContent (content) VALUES ('d')

     

    SELECT 'Header'

    UNION ALL

    SELECT content FROM tblContent

    UNION ALL

    SELECT 'Footer'

    What I couldn't do is to make sure that the values in the middle area are sorter. In other words you have something like

    Header

    a

    b

    c

    d

    e

    Footer

    instead of

    Header

    a

    c

    b

    e

    d

    Footer

    Any help would be much appreciated.

  • That'll work.

     

    CREATE TABLE tblContent (content varchar(2))

    INSERT INTO tblContent (content) VALUES ('a')

    INSERT INTO tblContent (content) VALUES ('c')

    INSERT INTO tblContent (content) VALUES ('b')

    INSERT INTO tblContent (content) VALUES ('e')

    INSERT INTO tblContent (content) VALUES ('d')

     

     

    SELECT 'Header' AS content, 1 AS Sort

    UNION ALL

    SELECT content, 2 AS Sort FROM tblContent

    UNION ALL

    SELECT 'Footer' aS content, 3 AS Sort

    ORDER BY Sort, Content

    DROP TABLE tblContent

  • Sorry guys, my mistake, my scenario wasn't quite accurate. I try to simplify it as much as I could, but I end up getting it wrong.

    Here I am try it again

    CREATE TABLE tblContent (content varchar(2))

    INSERT INTO tblContent (content) VALUES ('aa')

    INSERT INTO tblContent (content) VALUES ('mc')

    INSERT INTO tblContent (content) VALUES ('db')

    INSERT INTO tblContent (content) VALUES ('le')

    INSERT INTO tblContent (content) VALUES ('nd')

    SELECT 'Header'

    UNION ALL

    SELECT content FROM tblContent ORDER BY substring(content,2,1))

    UNION ALL

    SELECT 'Footer'

     

    The idea is that I have to sort by a string that is inside the content field.

    The privious one one would have worked acceptably, not guarantied 100%, with something like

    SELECT 'Header'

    UNION ALL

    SELECT content FROM (SELECT TOP 100 content FROM tblContent ORDER BY content) a

    UNION ALL

    SELECT 'Footer'

    if you know about how many rows you are expecting in the table.

    Thanks Ninja for help, but adding a Sort column would change the format of my output file.

     

  • You don't need to output the sort column to use it in the order by :

    CREATE TABLE tblContent (content varchar(2))

    INSERT INTO tblContent (content) VALUES ('1a')

    INSERT INTO tblContent (content) VALUES ('1c')

    INSERT INTO tblContent (content) VALUES ('1b')

    INSERT INTO tblContent (content) VALUES ('1e')

    INSERT INTO tblContent (content) VALUES ('1d')

     

    SELECT Content FROM

    (

    SELECT 'Header' AS content, 1 AS Sort

    UNION ALL

    SELECT content, 2 AS Sort FROM tblContent

    UNION ALL

    SELECT 'Footer' aS content, 3 AS Sort

    ) dt

    ORDER BY dt.Sort, CASE WHEN dt.Sort = 2 THEN substring(content,2,1) ELSE Content END

    DROP TABLE tblContent

  • Thanks, works beautifully, nice technique.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply