CODEWITHSUNDEEP
sql
ezechiele2517
ezechiele2517

Reputation: 375

How to order by 2 columns with empty field?

I have a question about ordering a SQL table. I can't find a solution on stack or google. My table seems as follows:

Code    Name        Referent    Phone        Email         
-----------------------------------------------------------
501     Paul                    0234343      [email protected]   
502     Andreas                 03245345     [email protected]
501                 Robert      027766543    [email protected]
504     Samuel                  0935345345   [email protected]
503     Emmanuel                0935345345   [email protected]

I want to order first by Name and after by Code but I want the same codes under the same code raw as follows:

Code    Name        Referent    Phone        Email         
-----------------------------------------------------------
502     Andreas                 03245345     [email protected]
503     Emmanuel                0935345345   [email protected]
501     Paul                    0234343      [email protected]   
501                 Robert      027766543    [email protected]
504     Samuel                  0935345345   [email protected]

As you can see I want to have for column Code with 501 first the item with Name column not empty and after the column with 501 code and empty Name column.

@Rob Using COALESCE(Name, Referent), Code I get rows ordered by Name alphabetically as I want but not also by code for example I have:

Code    Name        Referent                   
---------------------------------
502     Andreas      NULL
501     Caesar       NULL               
503     Emmanuel     NULL
504     Marcus       NULL 
501     NULL        Marcel

As you can see 501 with null name not appears under Caesar but under 504 (Marcus), alphabetically it seems that Referent column follows Name column with 'M' chars.

Upvotes: 1

Views: 300

Answers (4)

dnoeth
dnoeth

Reputation: 60513

If your DBMS supports Windowed Aggregates it's simple:

order by max(name) over (partition by code)
  ,name nulls last

Otherwise you need a Scalar Subquery (which might be very inefficient):

from mytable as t1
...
order by (select max(name) from mytable as t2 where t1.code = t2.code)
  ,name nulls last

Upvotes: 0

Thallius
Thallius

Reputation: 2619

I am not sure if this works but perhaps you can order with CONCAT

ORDER BY CONCAT(Name, Referent)

Never tried that and I cannot test it now. So sorry if it is nonsens :)

Should Work, if you separate the Strings Using Pipe, at least something ordered Low, Like ' impossible '

Concat(Name,'     ',Referent)

Upvotes: 1

Rob
Rob

Reputation: 45809

You can use the COALESCE function in your ORDER BY. Assuming the table is called Names the following query would achieve this:

SELECT  *
FROM    [Names]
ORDER
BY      COALESCE(Name, Referent), 
        Code

What the COALESCE function is doing is basically saying, "return the value from the first of the columns listed as parameters that is not null". This is then being used by the ORDER BY to determine the order of the rows that are returned.

This does pre-suppose that the data is stored as a NULL, rather than an empty string. If it's actually stored as an empty string, then you can work in the NULLIF function to achieve this:

SELECT  *
FROM    [Names]
ORDER
BY      COALESCE(NULLIF(Name, ''), NULLIF(Referent, '')),
        Code

I've used the following test harness to put these queries together:

CREATE TABLE [Names]
(
    Code INT,
    Name NVARCHAR(100),
    Referent NVARCHAR(100),
    Phone NVARCHAR(100),
    Email NVARCHAR(100)
)

INSERT
INTO    [Names]
        (
            Code,
            Name,
            Referent,
            Phone,
            Email
        )
VALUES  ( 501, 'Paul', NULL, '0234343', '[email protected]' ),
        ( 502, 'Andreas', NULL, '03245345', '[email protected]' ),
        ( 501, NULL, 'Robert', '027766543', '[email protected]' ),
        ( 504, 'Samuel', NULL, '0935345345', '[email protected]' ),
        ( 503, 'Emmanuel', NULL, '0935345345', '[email protected]')

Upvotes: 3

Dhaval Asodariya
Dhaval Asodariya

Reputation: 558

Here is the solution to order by two columns at a time.

 SELECT * FROM MyTable 
 ORDER BY Name, Code

OR

 SELECT *, (Name + Code) AS NameCode FROM MyTable 
 ORDER BY NameCode

Upvotes: 0

Related Questions