How to prioritise a string in select statement

This is an example using SQL Server showing how to return data when you have a string priority.

 

For this example we are using a car dealership as an example.  

 

1. Create a table called "cars"

 CREATE TABLE cars
(
carsID INT IDENTITY(1,1) CONSTRAINT pk_cars PRIMARY KEY NOT NULL
,model VARCHAR(20) NOT NULL
,colour VARCHAR(20) NOT NULL
,cost Decimal(8,2) NOT NULL
)

 

2. Insert some sample data

INSERT INTO cars (model,colour,cost) VALUES ('Jetta','White',3995.00)
INSERT INTO cars (model,colour,cost) VALUES ('Polo','Black',7995.00)
INSERT INTO cars (model,colour,cost) VALUES ('Polo','Red',6995.00)
INSERT INTO cars (model,colour,cost) VALUES ('Polo','White',6995.00)
INSERT INTO cars (model,colour,cost) VALUES ('Golf','Black',12995.00)
INSERT INTO cars (model,colour,cost) VALUES ('Golf','White',11995.00)

 

What we want to return is a list of models (1 of each) with a priority on colour.

 

If there is a Black model, return that, otherwise, if there is a Red model return that, or lastly if there is a White model return that.

 

1. Black
2. Red
3. White

 

This is how the data looks in our table:

carsID	model	colour	cost
1	Jetta	White	3995.00
2	Polo	Black	7995.00
3	Polo	Red	6995.00
4	Polo	White	6995.00
5	Golf	Black	12995.00
6	Golf	White	11995.00

 

So if I've explained the example clearly, you should understand that this is the result set that we want to achieve. 

carsID	model	colour	cost
5	Golf	Black	12995.00
1	Jetta	White	3995.00
2	Polo	Black	7995.00

 

To achieve this we can use the Rank() Over function:

SELECT 
s.carsID,
s.model,
s.colour,
s.cost
FROM (SELECT t.*,
RANK() OVER (PARTITION BY t.model
ORDER BY t.colour) AS rank
FROM cars t
WHERE t.colour IN ('Black', 'Red','White')) s
WHERE	s.rank = 1

Comment

---