How to prioritise a string in select statement

Posted 15 May 17 by Darren Welch

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




Post Comments

Darren Welch

By

Darren Welch


  15 May 17


Contact me online

 Facebook

 Twitter

 Linked In

 Git Hub

Related Content




Connect to SQl Server from command prompt

05 Feb 2017




Send ctrl alt delete over remote desktop

05 Feb 2017




Using Flickr API and JSON to load Content

06 Jun 2017