How to prioritise a string in select statement

15 May 17

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




Comments

Darren Welch

By

Darren Welch


  15 May 17


Contact me online

 Facebook

 Twitter

 Linked In

 Git Hub