CODEWITHSUNDEEP
sqlsql-serversubquery
Saravana Santhosh
Saravana Santhosh

Reputation: 197

Sub Query - Return NULL, if it does not match the highest

I want to select the number of highest balance from a table for my test project.

For example, if user says, he wants to know 5th highest balance, my query should return corresponding name.

Here is sample test table for the scenario:

CREATE TABLE ACCT_MSTR(
    ACCTID INTEGER PRIMARY KEY,
    NAME VARCHAR(50),
    CITY VARCHAR(20),
    BRID VARCHAR(5),
    DOO DATETIME,
    CBALANCE MONEY,
    UBALANCE MONEY,
    STATUS VARCHAR(1)
)

INSERT INTO ACCT_MSTR VALUES(101, 'SARA', 'BLR', 'BR1', GETDATE(), 1000, 1000, 'A')
INSERT INTO ACCT_MSTR VALUES(102, 'SANTHOSH', 'BLR', 'BR10', GETDATE() - 40, 5500, 100, 'A')
INSERT INTO ACCT_MSTR VALUES(103, 'KAVIN', 'GOBI', 'GI20', GETDATE() - 100, 2500, 10500, 'U')
INSERT INTO ACCT_MSTR VALUES(104, 'ARAVIND', 'GOBI', 'GI20', GETDATE() - 20, 10000, 5500, 'A')
INSERT INTO ACCT_MSTR VALUES(105, 'BALA', 'HYD', 'HYD2', GETDATE()-1000, 1000, 500, 'U')
INSERT INTO ACCT_MSTR VALUES(106, 'PRABU', 'TUP', 'TUP25', GETDATE() - 1000, 7500, 1000, 'A')
INSERT INTO ACCT_MSTR VALUES(107, 'ANBU', 'CHE', 'CHE1', GETDATE()-250, 8000, 1000, 'A')
INSERT INTO ACCT_MSTR VALUES(108, 'AMAR', 'CHE', 'CHE3', GETDATE()-2500, 7800, 1000, 'U')
INSERT INTO ACCT_MSTR VALUES(109, 'DHINESH', 'CBE', 'CBE1', GETDATE()-150, 3500, 1000, 'A')
INSERT INTO ACCT_MSTR VALUES(110, 'DUPARAVIND', 'GOBI', 'GI20', GETDATE() - 20, 10000, 5500, 'A')
INSERT INTO ACCT_MSTR VALUES(111, 'DUPSANTHOSH', 'BLR', 'BR10', GETDATE() - 40, 5500, 100, 'A')

I have used the following query (for the 5th highest):

SELECT NAME FROM ACCT_MSTR
WHERE CBALANCE = ( SELECT MIN(CBALANCE)
                    FROM ACCT_MSTR
                    WHERE CBALANCE IN ( SELECT DISTINCT TOP 5 CBALANCE 
                                        FROM ACCT_MSTR 
                                        ORDER BY CBALANCE DESC
                                    )
                )

If I want to know the 15th highest which is invalid(Total number of record in the table itself 12 only), it should return NULL. Instead, it is returning the last highest value from the table, since i used TOP 15.

Any idea to return NULL, if it does not match the given condition in this case.

Upvotes: 1

Views: 80

Answers (1)

Andomar
Andomar

Reputation: 238076

Assuming you're using SQL Server, you could:

declare @name varchar(50)

select @name = name
from   (
       select  row_number() over (order by CBALANCE desc) rn
       ,       name
       from    acct_mstr
       ) as SubQueryAlias
where  rn = 15

-- Return a rowset of one row, even if no row matched
-- the where clause above
select  @name as name

Upvotes: 2

Related Questions