Select a Default Row in SQL

• Download source code - 1.8 KB

Background

A few days ago, I had to return a default row if no row was found in a table for a specific logic. I was wondering, what would be the best way to do this? Finally, I was successful with the SQL Server database. A couple of days later, I had to do the same in Oracle and MySQL database. Unexpectedly, it wasn't generating the expected result with the same approach. After a few changes to the query, it was working as expected. Today, I am going to share a few sample codes that do the same thing but in different databases.

Table And Data

Here is our input data table:

/*table*/
--DROP TABLE People;
CREATE TABLE People(
  Name VARCHAR(100),
  AreaId VARCHAR(100),
  IsActive INTEGER
);

/*data*/
--DELETE FROM People;
INSERT INTO People VALUES('Den', 'A', 1);                /*candidate*/
INSERT INTO People VALUES('Han', 'A', 1);                /*best candidate, with conditional 
                                                           filter ORDER BY NAME DESC, 
                                                           this row will come first*/
INSERT INTO People VALUES('Ben', 'A', 0);                /*inactive row*/
INSERT INTO People VALUES('Aaron', 'Default', 1);        /*Default row, will be selected if 
                                                           no active row found*/    

SELECT * FROM People;

Scenario: There are many different rows in People table.

1. We need to select one active row (not default row) for a particular area.
2. If multiple active rows are found for the same area, a user will be prioritized based on names descending order.
3. If no active row is found, we will return the default area row.

Toward Solution: SQL Server

My First Attempt

Not working as expected.

/*First try: not working as expected*/
SELECT 
    TOP 1 *
FROM (
    SELECT * FROM People WHERE IsActive = 1 AND AreaId = 'A'
    UNION
    SELECT * FROM People WHERE AreaId = 'Default'
) P

Solution Found on Web

http://stackoverflow.com/questions/285666/how-to-set-a-default-row-for-a-query-that-returns-no-rows

SELECT 
    TOP 1 *
FROM (
    SELECT * FROM People WHERE IsActive = 1 AND AreaId = 'A'
    UNION
    SELECT * FROM People WHERE AreaId = 'Default'
    AND NOT EXISTS(
        /*repeating same query twice*/
        SELECT * FROM People WHERE IsActive = 1 AND AreaId = 'A'
    )
) P

It almost resolved my problem. I just needed to take care of a few things:

1. I needed to add more conditions or set priority to the candidate rows.
2. It is repeating a part of a query multiple times.

Let's check the queries for different databases.

SQL Server

Get Any Candidate Row

Working!!!

/*Regular:*/
SELECT 
     TOP 1 P.*
FROM (
    SELECT 
        P.*, NULL PriorityNo
    FROM People P WHERE IsActive = 1 AND AreaId = 'A'
    UNION
    SELECT 
        P.*, 1 PriorityNo
    FROM People P WHERE AreaId = 'Default'
) P
ORDER BY PriorityNo ASC 

http://sqlfiddle.com/#!18/18288/2

Set Priority In Candidate Rows

Finally, I am able to consider all my logic without repeating the same codes multiple times.

/*More order:*/
WITH SelectedOrderedPeople
AS
(
    SELECT 
        P.*, 
        NULL PriorityNo,
        ROW_NUMBER() OVER(ORDER BY Name DESC) OrderId
    FROM People P WHERE IsActive = 1 AND AreaId = 'A'
)
, DefaultPeople
AS
(
    SELECT 
        P.*, 
        1 PriorityNo,
        NULL OrderId
    FROM People P WHERE AreaId = 'Default'
)
, RankedPeople
AS
(
    SELECT P.*, ROW_NUMBER() OVER(ORDER BY PriorityNo ASC, OrderId ASC) RankNo
    FROM (
        SELECT * FROM SelectedOrderedPeople
        UNION
        SELECT * FROM DefaultPeople
    ) P
)
SELECT 
    Name, AreaId, IsActive, RankNo
FROM RankedPeople
WHERE RankNo = 1;

http://sqlfiddle.com/#!18/18288/3

Oracle

Trying to do things like the SQL Server but it was always returning the default area row, no matter what.

Not Working Like SQL Server

/*not working like sql server*/
SELECT 
     P.*
FROM (
    SELECT 
        P.*, NULL PriorityNo
    FROM People P WHERE IsActive = 1 AND AreaId = 'A'
    UNION
    SELECT 
        P.*, 1 PriorityNo
    FROM People P WHERE AreaId = 'Default'
) P
WHERE ROWNUM = 1
ORDER BY PriorityNo ASC /*or DESC*/

http://sqlfiddle.com/#!4/4693b/1

So I am going to change the existing queries to:

Get Any Candidate Row

/*Regular:*/
WITH SelectedOrderedPeople
AS
(
    SELECT 
        P.*, 
        NULL PriorityNo
    FROM People P WHERE IsActive = 1 AND AreaId = 'A'
)
, DefaultPeople
AS
(
    SELECT 
        P.*, 
        1 PriorityNo
    FROM People P WHERE AreaId = 'Default'
)
, RankedPeople
AS
(
    SELECT P.*, ROW_NUMBER() OVER(ORDER BY PriorityNo DESC) RankNo   /*not like sql server,
                                                                       using DESC*/
    FROM (
        SELECT * FROM SelectedOrderedPeople
        UNION
        SELECT * FROM DefaultPeople
    ) P
)
SELECT 
    Name, AreaId, IsActive, RankNo
FROM RankedPeople
WHERE RankNo = 1;

http://sqlfiddle.com/#!4/4693b/3

Set Priority In Candidate Rows

/*More order:*/
WITH SelectedOrderedPeople
AS
(
    SELECT 
        P.*, 
        NULL PriorityNo,
        ROW_NUMBER() OVER(ORDER BY Name DESC) OrderId
    FROM People P WHERE IsActive = 1 AND AreaId = 'A'
)
, DefaultPeople
AS
(
    SELECT 
        P.*, 
        1 PriorityNo,
        NULL OrderId
    FROM People P WHERE AreaId = 'Default'
)
, RankedPeople
AS
(
    SELECT P.*, ROW_NUMBER() OVER(ORDER BY PriorityNo DESC, OrderId ASC) RankNo  /*not like SQL
                                                                           server, using DESC*/
    FROM (
        SELECT * FROM SelectedOrderedPeople
        UNION
        SELECT * FROM DefaultPeople
    ) P
)
SELECT 
    Name, AreaId, IsActive, RankNo
FROM RankedPeople
WHERE RankNo = 1;

http://sqlfiddle.com/#!4/4693b/4

Working as expected.

MySQL

Get Any Candidate Row

/*Regular*/
SELECT 
     P.*
FROM (
    SELECT 
        P.*, NULL PriorityNo
    FROM People P WHERE IsActive = 1 AND AreaId = 'A'
    UNION
    SELECT 
        P.*, 1 PriorityNo
    FROM People P WHERE AreaId = 'Default'
) P
ORDER BY PriorityNo ASC
LIMIT 1;        /*auto ASC by name in non default rows*/

http://sqlfiddle.com/#!9/4693b/1

Set Priority In Candidate Rows

CTE wasn't working in my MySQL database. So changing the query to:

SELECT 
    *
FROM (
    SELECT * FROM (
        SELECT 
            S.*, 
            NULL PriorityNo
        FROM People S WHERE IsActive = 1 AND AreaId = 'A'
        ORDER BY Name DESC
    ) SelectedOrderedPeople
    UNION
    SELECT * FROM (
        SELECT 
            D.*, 
            1 PriorityNo
        FROM People D WHERE AreaId = 'Default'
    ) DefaultPeople
) RankedPeople
ORDER BY PriorityNo ASC
LIMIT 1;

http://sqlfiddle.com/#!9/4693b/2

PostgreSQL

Get Any Candidate Row

/*Regular:*/
SELECT 
     P.*
FROM (
    SELECT 
        P.*, NULL PriorityNo
    FROM People P WHERE IsActive = 1 AND AreaId = 'A'
    UNION
    SELECT 
        P.*, 1 PriorityNo
    FROM People P WHERE AreaId = 'Default'
) P
ORDER BY PriorityNo DESC        /*sql server was ASC*/
LIMIT 1;

http://sqlfiddle.com/#!17/4693b/2

Set Priority In Candidate Rows

/*More order*/
WITH SelectedOrderedPeople
AS
(
    SELECT 
        P.*, 
        CAST(NULL AS NUMERIC) PriorityNo,
        CAST(ROW_NUMBER() OVER(ORDER BY Name DESC) AS NUMERIC) OrderId
    FROM People P WHERE IsActive = 1 AND AreaId = 'A'
)
, DefaultPeople
AS
(
    SELECT 
        P.*, 
        CAST(1 AS NUMERIC) PriorityNo,
        CAST(NULL AS NUMERIC) OrderId
    FROM People P WHERE AreaId = 'Default'
)
, RankedPeople
AS
(
    SELECT P.*, ROW_NUMBER() OVER(ORDER BY PriorityNo DESC, OrderId ASC) RankNo  /*SQL server 
                                                                                 both was ASC*/
    FROM (
        SELECT * FROM SelectedOrderedPeople
        UNION
        SELECT * FROM DefaultPeople
    ) P
)
SELECT 
    Name, AreaId, IsActive, RankNo
FROM RankedPeople
WHERE RankNo = 1;

http://sqlfiddle.com/#!17/4693b/3

Without Using CTE

SELECT 
    Name, AreaId, IsActive, RankNo
FROM (
    SELECT P.*, ROW_NUMBER() OVER(ORDER BY PriorityNo ASC, OrderId ASC) RankNo /*need to 
                                           change PriorityNo ASC to DESC depending on db*/
    FROM (
        SELECT * FROM (
            SELECT 
                S.*, 
                NULL PriorityNo,
                ROW_NUMBER() OVER(ORDER BY Name DESC) OrderId
            FROM People S WHERE IsActive = 1 AND AreaId = 'A'
        ) SelectedOrderedPeople
        UNION
        SELECT * FROM (
            SELECT 
                D.*, 
                1 PriorityNo,
                NULL OrderId
            FROM People D WHERE AreaId = 'Default'
        ) DefaultPeople
    ) P
) RankedPeople
WHERE RankNo = 1;

"ORDER BY" In Different DB

ORDER BY is a bit different in different databases for NULL value. If you closely look at the above queries, we have been switching between ASC and DESC for PriorityNo column, depending on the database. Let us check, how it is actually working in different databases.

Table And Data

/*table*/
CREATE TABLE ValueTest(
  Id INTEGER NULL
);

/*data*/
INSERT INTO ValueTest VALUES (-1);
INSERT INTO ValueTest VALUES (NULL);
INSERT INTO ValueTest VALUES (0);
INSERT INTO ValueTest VALUES (1);

/*result*/
SELECT * FROM ValueTest ORDER BY Id ASC;

Result

/*
SQL Server    MySQL      Oracle    PostgreSQL
----------    ------    ------    ----------
   NULL        NULL       -1         -1
   -1          -1          0          0
    0           0          1          1
    1           1         NULL       NULL
*/

Limitations

The code may throw unexpected errors for untested inputs. If any, just let me know.

Code Source

Please find the SQL queries as an attachment.

History

• 22^nd July, 2019: Initial version