SQL - SELECT with default if null

user_979331 0
Viewed 952

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.

sql-server sql
4 Answers

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 |
+----+-------+-------+-----------+
user_23334990

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
user_25248300 
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

user_86757780

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

user_33579370
Related