SQL - SELECT with default if null

Question

I have a query like so:

SELECT id, name, price, floorplan 
FROM Inventory 
ORDER BY price

This will return the id, name, price, floorplan from my Inventory table ordered by the price. With this query I get 3 rows returned, the last row has a floorplan value, the other two are null. Is it possible get the non-null floorplan to replace the null floorplan column? I don't want to group these as I need to get the 3 rows returned.

wow you gave a lot of bounties..one suggestion though, for sql questions expected result and current result,plus posting what you have tried will help — TheGameiswar
– TheGameiswar, Commented Oct 4, 2017 at 17:39

SqlZim · Accepted Answer · 2017-10-04 17:46:51Z

4

You could use a window function aggregate like max() over():

select 
    id
  , name
  , price
  , max(floorplan) over () as floorplan
from Inventory 
order by price

rextester demo: http://rextester.com/GDWH85581

with this test setup:

create table inventory (id int, name varchar(32), price decimal(9,2), floorplan int)
insert into inventory values 
 (1,'one',1.01,null)
,(2,'two',2.02,null)
,(3,'three',3.03,1024)

returns:

+----+-------+-------+-----------+
| id | name  | price | floorplan |
+----+-------+-------+-----------+
|  1 | one   | 1.01  |      1024 |
|  2 | two   | 2.02  |      1024 |
|  3 | three | 3.03  |      1024 |
+----+-------+-------+-----------+

answered Oct 4, 2017 at 17:46

SqlZim

38.2k6 gold badges45 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TheGameiswar Over a year ago

This doesn't require accessing table twice

Valli · Accepted Answer · 2017-10-04 18:16:17Z

3

Find the maximum of floorplan. Using the isnull function, replace floorplan by its max value, whenever it is null.

  SELECT id, 
         name, 
         price, 
         isnull(floorplan, (select max(floorplan) from Inventory) )
    FROM Inventory 
ORDER BY price

answered Oct 4, 2017 at 18:16

Valli

1,4601 gold badge8 silver badges14 bronze badges

3 Comments

S3S Over a year ago

isnull isn't needed here and this logic fails if there are more than 1 rows where the value isn't NULL. It would then, bring back extra rows. It'd be wiser to just use max() and remove the conditional check.

Valli Over a year ago

@scsimon Am just trying to understand your comment.. You mean that it will have an error 'subquery returning more than one row'? But there is a max function in the subquery, so we will not get more than one row anytime

S3S Over a year ago

Sorry I wasn't clear. You will not receive an error. I only implied that your logic is to replace all NULL values with the max value of that table which. The OP didn't specify if there'd be more rows with non-null values, but if there are, then i like your method simply because it keeps the integrity of the previous rows. Hoever, based on the sample, you simply only need what @SqlZim has suggested.

Bave · Accepted Answer · 2017-10-04 17:36:25Z

2

SELECT id, name, price, 
       (SELECT TOP 1 floorplan FROM Inventory WHERE floorplan IS NOT NULL) as [floorplan]
  FROM Inventory

This should work with your 3 rows but I put in a TOP 1 if there will be more records. You need to specify which floorplan you want to see if more than 1 is not null

answered Oct 4, 2017 at 17:36

Bave

2451 silver badge6 bronze badges

4 Comments

S3S Over a year ago

Using TOP without an ORDER BY implies you don't care which rows is returned. The results could vary with each query @user979331. Something to bu VERY cautious about.

Bave Over a year ago

that is wrong. not having an order by doesnt mean that you get different results every time. you get the same result every time if no data is added. it is by default ordered by the clustered key which should in this case be the id

S3S Over a year ago

Your if no data is added defeats the purpose of transaction databases like SQL Server. That's a bold assumption to make. Regardless, you should read this. You have chosen the worst way to go about this.

S3S Over a year ago

I read your answer, and that doesn't change my comment what so ever.

Lee · Accepted Answer · 2017-10-04 17:50:34Z

1

Consider this example. I think the subquery should be a correlated one. If not dryer gets floorplanned but there isnt one in the data. Thanks

    CREATE TABLE Inventory 
    (id int 
    ,name varchar(30)
    ,price money
    ,floorplan char(1)
    )

    insert inventory 
    SELECT 1, 'Washer', 300, NULL
    insert inventory 
    SELECT 1, 'Washer', 330, NULL
    insert inventory 
    SELECT 1, 'Washer', 340, 'Y'
    insert inventory 
    SELECT 2, 'Dryer', 275,  NULL

    SELECT id, name, price, 
        (SELECT TOP 1 floorplan FROM Inventory AS Y WHERE floorplan IS NOT NULL
        AND Y.id = I.id) as [floorplan]
    FROM Inventory AS I

http://sqlfiddle.com/#!6/ca73e6/3

answered Oct 4, 2017 at 17:50

Lee

1897 bronze badges

2 Comments

TheGameiswar Over a year ago

I think this is same as other answer and user mentioned only three rows,so i believe no need of correlation

Lee Over a year ago

Paste first query in sqlfiddle and Dryer has floorplan 'Y'. That is the correct behavior?

Collectives™ on Stack Overflow

SQL - SELECT with default if null

4 Answers 4

1 Comment

3 Comments

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

3 Comments

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related