Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2013
    Posts
    46

    Unanswered: How to make NULL values show last

    I have the following table records structure:
    Code:
    drop table ##t1
    
     create table ##t1(col1 int, col2 int, col3 int, col4 int)
    insert into ##t1(col1 , col2, col3, col4)
    values (1,2,3,4),
    (5,6,7,8)
    
    
    alter table ##t1 add col5 int, col6 int, col7 int, col8 int
    insert into ##t1(col5 , col6, col7, col8)
    values (1,2,3,4),
    (5,6,7,8), 
    (9,10,11,12)
    
    alter table ##t1 add col9 int, col10 int, col11 int, col12 int
    
    insert into ##t1(col9 , col10, col11, col12)
    values (1,2,3,4),
    (5,6,7,8)
    
    select * from ##t1
    I can't figure out how to put all nulls at the bottom and have all of my records on the top.
    Last edited by gvee; 06-24-14 at 05:50. Reason: [code] block added

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Sets explicitly have no order, although result sets can be ordered by the SELECT statement. What order would you like to impose on your rows?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2013
    Posts
    46
    the same order just null values at the bottom
    Code:
    col1	col2	col3	col4	col5	col6	col7	col8	col9	col10	col11	col12
    1	2	3	4	1	2	3	4	1	2	3	4
    5	6	7	8	5	6	7	8	5	6	7	8
    null	null	null	null	9	10	11	12	null    null
    Last edited by gvee; 06-24-14 at 05:50. Reason: [code] block added

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    So you don't want to change the order of the rows at all, but want to do something like a columnar coalesce() ???

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    well I suppose you could use an order by clause and set an outlying value for nulls using the isnull function...
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    ORDER
        BY CASE WHEN col1 IS NULL THEN 937 ELSE -937 END
         , col1
    P.S. your resultset is not what you think it is
    George
    Home | Blog

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by gvee View Post
    P.S. your resultset is not what you think it is
    I think that the result set that M1N wants isn't anything like what you and Healdem have envisioned. I'm waiting for confirmation, but if I understand the request then the idea intrigues me!

    It will be interesting to see if/how M1N responds to my previous question.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Feb 2013
    Posts
    46
    PatP yes like using coalesce.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is such deliciously drug induced SQL!
    Code:
    drop table ##t1
    
     create table ##t1(col1 int, col2 int, col3 int, col4 int)
    insert into ##t1(col1 , col2, col3, col4)
    values (1,2,3,4),
    (5,6,7,8)
    
    
    alter table ##t1 add col5 int, col6 int, col7 int, col8 int
    insert into ##t1(col5 , col6, col7, col8)
    values (1,2,3,4),
    (5,6,7,8), 
    (9,10,11,12)
    
    alter table ##t1 add col9 int, col10 int, col11 int, col12 int
    
    insert into ##t1(col9 , col10, col11, col12)
    values (1,2,3,4),
    (5,6,7,8)
    
    ; WITH x1 AS (
       SELECT  Row_Number() OVER (ORDER BY col1) AS rn, col1 AS c
          FROM ##t1 WHERE col1 IS NOT NULL
    ), x2 AS (
       SELECT  Row_Number() OVER (ORDER BY col2) AS rn, col2 AS c
          FROM ##t1 WHERE col2 IS NOT NULL
    ), x3 AS (
       SELECT  Row_Number() OVER (ORDER BY col3) AS rn, col3 AS c
          FROM ##t1 WHERE col3 IS NOT NULL
    ), x4 AS (
       SELECT  Row_Number() OVER (ORDER BY col4) AS rn, col4 AS c
          FROM ##t1 WHERE col4 IS NOT NULL
    ), x5 AS (
       SELECT  Row_Number() OVER (ORDER BY col5) AS rn, col5 AS c
          FROM ##t1 WHERE col5 IS NOT NULL
    ), x6 AS (
       SELECT  Row_Number() OVER (ORDER BY col6) AS rn, col6 AS c
          FROM ##t1 WHERE col6 IS NOT NULL
    ), x7 AS (
       SELECT  Row_Number() OVER (ORDER BY col7) AS rn, col7 AS c
          FROM ##t1 WHERE col7 IS NOT NULL
    ), x8 AS (
       SELECT  Row_Number() OVER (ORDER BY col8) AS rn, col8 AS c
          FROM ##t1 WHERE col8 IS NOT NULL
    ), x9 AS (
       SELECT  Row_Number() OVER (ORDER BY col9) AS rn, col9 AS c
          FROM ##t1 WHERE col9 IS NOT NULL
    ), x10 AS (
       SELECT  Row_Number() OVER (ORDER BY col10) AS rn, col10 AS c
          FROM ##t1 WHERE col11 IS NOT NULL
    ), x11 AS (
       SELECT  Row_Number() OVER (ORDER BY col11) AS rn, col11 AS c
          FROM ##t1 WHERE col11 IS NOT NULL
    ), x12 AS (
       SELECT  Row_Number() OVER (ORDER BY col12) AS rn, col12 AS c
          FROM ##t1 WHERE col12 IS NOT NULL
    ), ri AS (
       SELECT rn, Count(*) AS rc
          FROM (SELECT rn FROM x1 UNION  SELECT rn FROM x2 UNION
             SELECT rn FROM x3 UNION  SELECT rn FROM x4 UNION
             SELECT rn FROM x5 UNION  SELECT rn FROM x6 UNION
             SELECT rn FROM x7 UNION  SELECT rn FROM x8 UNION
             SELECT rn FROM x9 UNION  SELECT rn FROM x10 UNION
             SELECT rn FROM x11 UNION SELECT rn FROM x12) AS z
          GROUP BY rn)
    SELECT -- ri.rn, ri.rc, 
       x1.c, x2.c, x3.c, x4.c, x5.c, x6.c
    ,  x7.c, x8.c, x9.c, x10.c, x11.c, x12.c
       FROM ri
       LEFT JOIN x1 ON (x1.rn = ri.rn)
       LEFT JOIN x2 ON (x2.rn = ri.rn)
       LEFT JOIN x3 ON (x3.rn = ri.rn)
       LEFT JOIN x4 ON (x4.rn = ri.rn)
       LEFT JOIN x5 ON (x5.rn = ri.rn)
       LEFT JOIN x6 ON (x6.rn = ri.rn)
       LEFT JOIN x7 ON (x7.rn = ri.rn)
       LEFT JOIN x8 ON (x8.rn = ri.rn)
       LEFT JOIN x9 ON (x9.rn = ri.rn)
       LEFT JOIN x10 ON (x10.rn = ri.rn)
       LEFT JOIN x11 ON (x11.rn = ri.rn)
       LEFT JOIN x12 ON (x12.rn = ri.rn)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Feb 2013
    Posts
    46
    PatP thank you very much for your help I learned something new today I have never used over before!

  11. #11
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    As an aside, the ORDER BY clause in ANSI/ISO Standard SQL allows your set the order of NULLs in a cursor as FIRST or LAST. Otherwise, it makes no sense in a query.

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Celko View Post
    As an aside, the ORDER BY clause in ANSI/ISO Standard SQL allows your set the order of NULLs in a cursor as FIRST or LAST. Otherwise, it makes no sense in a query.
    Agreed, but what M1N wanted was to sort the COLUMNS, not the rows like an ORDER BY clause would do. I managed that sorting using the ORDER BY clause within the OVER. The problem intrigued me, I've never had a request quite like that (and I'm getting jaded enough to be interested in problems that I haven't seen before).

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •