CODEWITHSUNDEEP
sqlt-sql
cgatian
cgatian

Reputation: 22994

Alternative Way To Approach SQL statment If no rows exist, show all

Wondering if anyone out there might know a trick to a small sql script.

http://sqlfiddle.com/#!3/09638/3

I am looking to return only the rows that have a manual transmission and are Ford make. If no rows exist, then return all Ford make vehicles. I currently doing it using an IF EXISTs condition. I considered using a temporary table to store the first set of data, then looking at the rowcount() (rows inserted == 0) of the table to see if I needed to insert more data. There may be no other way to do it then my two options I described. Maybe the community has some thought on it.

Example DDL

CREATE TABLE Cars
(
  Make varchar(50),
  Model varchar(50),
  ManualTransmission bit
);


INSERT INTO Cars
(Make, Model, ManualTransmission)
VALUES
('Ford', 'Taurus', 0),
('Ford', 'Contour', 0),
('Ford', 'Mustang', 0),
('Jeep', 'Liberty', 1),
('Jeep', 'Cherokee', 0);

Upvotes: 1

Views: 616

Answers (4)

Martin Smith
Martin Smith

Reputation: 453618

One way

WITH CTE
     AS (SELECT *,
                RANK() OVER (ORDER BY ManualTransmission DESC) AS Rnk
         FROM   Cars
         WHERE  Make = 'Ford')
SELECT Make,
       Model,
       ManualTransmission
FROM   CTE
WHERE  Rnk = 1

Or another

SELECT TOP 1 WITH TIES Make,
                       Model,
                       ManualTransmission
FROM   Cars
WHERE  Make = 'Ford'
ORDER  BY ManualTransmission DESC

Both of these answers exploit the fact that ManualTransmission is a BIT datatype and 1 is the maximum possible value it can have. If ManualTransmission is nullable then you would need to change them to 

ORDER BY ISNULL(ManualTransmission,0) DESC

Or

ORDER BY CASE WHEN ManualTransmission = 1 THEN 0 ELSE 1 END

The CASE version would also be adaptable for more complex conditions.

Upvotes: 6

MarkD
MarkD

Reputation: 5316

I think I found a fairly cheap method? It seems to be cheaper because it does not need to do any sorting

;WITH CTE AS
(
    SELECT   *
            ,HasManual = SUM(CAST(ManualTransmission AS INT)) OVER (PARTITION BY Make)
    FROM Cars
)
SELECT Make, Model, ManualTransmission
FROM CTE 
WHERE   (Make = 'Ford' AND ManualTransmission = 1)
OR      (Make = 'Ford' AND HasManual = 0)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270431

Try this:

select *
from (select *,
             max(cast(ManualTransmission as int)) over (partition by make) as hasManual
      from cars
      where make = 'Ford'
     ) t
where hasManual = 0 or ManualTransmission = 1

Upvotes: 2

Paul Liebrand
Paul Liebrand

Reputation: 163

You could try the following:

SELECT * FROM Cars
 WHERE Make = COALESCE('Ford', Make)
   AND ManualTransmission = COALESCE(1,ManualTransmission)

Upvotes: 0

Related Questions