CODEWITHSUNDEEP
sqlsql-servernullaggregate-functions
Doonie Darkoo
Doonie Darkoo

Reputation: 1495

Treat NULL as largest possible value

I want to get the row with maximum Transaction number Grouped on the basis of Code.

CREATE TABLE SaleOrder
(
    TransactionNo Int,
    SaleOrderDate DATE,
    Code VARCHAR(25),
    Quantity INT,
    TotalAmount Numeric(18,2),
    Remarks VARCHAR(25)
)

INSERT INTO SaleOrder VALUES (NULL, '2018-10-01', 'SO-001-OCT-18',  6, 2500, 'Hello');
INSERT INTO SaleOrder VALUES (1,    '2018-10-01', 'SO-001-OCT-18',  8, 2600, 'Hello');
INSERT INTO SaleOrder VALUES (2,    '2018-10-01', 'SO-001-OCT-18', 12, 3400, 'Hello');
INSERT INTO SaleOrder VALUES (3,    '2018-10-01', 'SO-001-OCT-18',  9, 2900, 'Hello');
INSERT INTO SaleOrder VALUES (4,    '2018-10-01', 'SO-001-OCT-18',  2,  900, 'Hello');
INSERT INTO SaleOrder VALUES (NULL, '2018-10-01', 'SO-002-OCT-18',  6, 2500, 'Hello');
INSERT INTO SaleOrder VALUES (NULL, '2018-10-01', 'SO-003-OCT-18',  6, 2500, 'Hello');
INSERT INTO SaleOrder VALUES (0,    '2018-10-01', 'SO-004-OCT-18',  6, 2500, 'Hello');


SELECT * FROM SaleOrder O
WHERE TransactionNo  = (SELECT MAX(ISNULL(TransactionNo, 1)) FROM SaleOrder GROUP BY Code)

Here when TransactionNo is NULL it's not returning any record against it while it should return that too.

Upvotes: 2

Views: 117

Answers (6)

Salman Arshad
Salman Arshad

Reputation: 272406

There is absolutely no reason to treat NULL as largest possible value. You can always use the ROW_NUMBER trick:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Code ORDER BY TransactionNo DESC) AS RN
    FROM SaleOrder
)
SELECT * FROM cte
WHERE RN = 1

Result:

| TransactionNo | SaleOrderDate | Code          | Quantity | TotalAmount | Remarks | RN |
|---------------|---------------|---------------|----------|-------------|---------|----|
| 4             | 2018-10-01    | SO-001-OCT-18 | 2        | 900.00      | Hello   | 1  |
| NULL          | 2018-10-01    | SO-002-OCT-18 | 6        | 2500.00     | Hello   | 1  |
| NULL          | 2018-10-01    | SO-003-OCT-18 | 6        | 2500.00     | Hello   | 1  |
| 0             | 2018-10-01    | SO-004-OCT-18 | 6        | 2500.00     | Hello   | 1  |

Upvotes: 3

Pawel Czapski
Pawel Czapski

Reputation: 1864

Below code will give you much more info than you requested, you can play with it, add some comment if have any questions.

CREATE TABLE #SaleOrder
(
    TransactionNo Int,
    #SaleOrderDate DATE,
    Code VARCHAR(25),
    Quantity INT,
    TotalAmount Numeric(18,2),
    Remarks VARCHAR(25)
)

INSERT INTO #SaleOrder VALUES (NULL, '2018-10-01', 'SO-001-OCT-18', 6, '2500', 'Hello');
INSERT INTO #SaleOrder VALUES (1, '2018-10-01', 'SO-001-OCT-18', 8, '2600', 'Hello');
INSERT INTO #SaleOrder VALUES (2, '2018-10-01', 'SO-001-OCT-18', 12, '3400', 'Hello');
INSERT INTO #SaleOrder VALUES (3, '2018-10-01', 'SO-001-OCT-18', 9, '2900', 'Hello');
INSERT INTO #SaleOrder VALUES (4, '2018-10-01', 'SO-001-OCT-18', 2, '900', 'Hello');
INSERT INTO #SaleOrder VALUES (NULL, '2018-10-01', 'SO-002-OCT-18', 6, '2500', 'Hello');
INSERT INTO #SaleOrder VALUES (NULL, '2018-10-01', 'SO-003-OCT-18', 6, '2500', 'Hello');
INSERT INTO #SaleOrder VALUES (0, '2018-10-01', 'SO-004-OCT-18', 6, '2500', 'Hello');

-- final select
SELECT top 1 -- optional, if you want to return 1 record
    Code,
    sum(Quantity) as totalQuantity, 
    sum(TotalAmount) as totallAmount, 
    count(1) as totalOrdersPerCode
FROM #SaleOrder O
group by Code
order by count(1) desc  

-- drop temp table
drop table #SaleOrder

Upvotes: 0

Michał Turczyn
Michał Turczyn

Reputation: 37500

Try this:

select TransactionNo,SaleOrderDate,Code,Quantity,TotalAmount,Remarks from (
    select TransactionNo,SaleOrderDate,Code,Quantity,TotalAmount,Remarks, 
           row_number() over (partition by code order by transactionno desc) rn 
    from (
        select TransactionNo,SaleOrderDate,Code,Quantity,TotalAmount,Remarks,
               coalesce(transactionno, count(*) over (partition by code) + 1) transactionno2
        from SaleOrder
    ) a
) a where rn = 1

Explanation:

With this line coalesce(transactionno, count(*) over (partition by code) + 1) transactionno2 I assign greatest value per group (partitioned by code) where it's null. But be aware when you have two NULLs, rows will be tied in this case and it would be non-deterministic.

Upvotes: 0

Shushil Bohara
Shushil Bohara

Reputation: 5656

I think this ISNULL check should solve your problem and replace = with IN subquery can return multiple records

WHERE ISNULL(TransactionNo, 1) IN

Upvotes: 0

Ali Eshghi
Ali Eshghi

Reputation: 1243

when you use = select in your where clause its totally wrong because this is possible you have multiple records,so you have to change your code like this: 

SELECT MAX(ISNULL(TransactionNo, 1)),code FROM SaleOrder O
 GROUP BY Code

but if you want to return only one record you can use it like this:

SELECT * FROM SaleOrder O
WHERE TransactionNo  = (SELECT TOP 1 MAX(ISNULL(NULL, 1)) FROM SaleOrder GROUP BY Code)

Upvotes: 0

Sri
Sri

Reputation: 155

When TransactionNo is NULL and query returns more than one row that could not assigned to the filter

The below may help

SELECT * FROM SaleOrder O
WHERE TransactionNo  = (SELECT TOP 1 MAX(ISNULL(NULL, 1)) FROM SaleOrder GROUP BY Code)

Note that this could take any record with TransactionNo having a NULL value. Segregating the logic for TransactionNo filter would be easier to extend and maintain. Example below: 

DECLARE @TransactionNo int

SELECT TOP 1 @TransactionNo = MAX(ISNULL(TransactionNo, 1)) FROM SaleOrder GROUP BY Code -- (OR) Logic here

SELECT * FROM SaleOrder O
WHERE TransactionNo  = @TransactionNo

Upvotes: 0

Related Questions