How to select a row for certain (or give preference in the selection) in mysql?

Question

Need your help guys in forming a query.

Example. Company - Car Rental

Table - Cars

ID  NAME       STATUS
1   Mercedes   Showroom
2   Mercedes   On-Road

Now, how do I select only one entry from this table which satisfies the below conditions?

1. If Mercedes is available in Showroom, then fetch only that row. (i.e. row 1 in above example)

2. But If none of the Mercedes are available in the showroom, then fetch any one of the rows. (i.e. row 1 or row 2) - (This is just to say that all the mercedes are on-road)

Using distinct ain't helping here as the ID's are also fetched in the select statement

Thanks!

Answer 1

SELECT * FROM cars 
  WHERE name = 'Mercedes' 
  AND status = 'Showroom' 
UNION SELECT * FROM cars 
  WHERE name = 'Mercedes' 
LIMIT 1;

EDIT Removed the ALL on the UNION since we only want distinct rows anyway.

Answer 2

Here's a common way of solving that problem:

SELECT *,
CASE STATUS
WHEN 'Showroom' THEN 0
ELSE 1
END AS InShowRoom
FROM Cars
WHERE NAME = 'Mercedes'
ORDER BY InShowRoom
LIMIT 1

---

Here's how to get all the cars, which also shows another way to solve the problem:

SELECT ID, NAME, IFNULL(c2.STATUS, c1.STATUS)
FROM Cars c1
LEFT OUTER JOIN Cars c2
ON c2.NAME = c1.NAME AND c2.STATUS = 'Showroom'
GROUP BY NAME
ORDER BY NAME

Answer 3

You would want to use the FIND_IN_SET() function to do that.

SELECT *
FROM Cars
WHERE NAME = 'Mercedes'
ORDER BY FIND_IN_SET(`STATUS`,'Showroom') DESC
LIMIT 1

If you have a preferred order of other statuses, just add them to the second parameter.

ORDER BY FIND_IN_SET(`STATUS`,'On-Road,Showroom' ) DESC

To fetch 'best' status for all cars you can simply do this:

SELECT *
FROM Cars
GROUP BY NAME
ORDER BY FIND_IN_SET(`STATUS`,'Showroom') DESC

Answer 4

MySQL doesn't have ranking/analytic/windowing functions, but you can use a variable to simulate ROW_NUMBER functionality (when you see "--", it's a comment):

SELECT x.id, x.name, x.status
  FROM (SELECT t.id,
               t.name,
               t.status,
               CASE 
                 WHEN @car_name != t.name THEN @rownum := 1 -- reset on diff name
                 ELSE @rownum := @rownum + 1 
               END AS rank,
               @car_name := t.name -- necessary to set @car_name for the comparison
          FROM CARS t 
          JOIN (SELECT @rownum := NULL, @car_name := '') r
      ORDER BY t.name, t.status DESC) x  --ORDER BY is necessary for rank value
 WHERE x.rank = 1

Ordering by status DESC means that "Showroom" will be at the top of the list, so it'll be ranked as 1. If the car name doesn't have a "Showroom" status, the row ranked as 1 will be whatever status comes after "Showroom". The WHERE clause will only return the first row for each car in the table.

The status being a text based data type tells me your data is not normalized - I could add records with "Showroom", "SHOWroom", and "showROOM". They'd be valid, but you're looking at using functions like LOWER & UPPER when you are grouping things for counting, sum, etc. The use of functions would also render an index on the column useless... You'll want to consider making a CAR_STATUS_TYPE_CODE table, and use a foreign key relationship to make sure bad data doesn't get into your table:

DROP TABLE IF EXISTS `example`.`car_status_type_code`;
CREATE TABLE  `example`.`car_status_type_code` (
  `car_status_type_code_id` int(10) unsigned NOT NULL auto_increment,
  `description` varchar(45) NOT NULL default '',
  PRIMARY KEY  (`car_status_type_code_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;