I have a fruits table, with reference to country. I need to query the table and display the query result.
If a fruit appears in the table multiple times, i.e. from multiple countries, then only the result from one country should be shown. Further, the record should be selected based on the priority of the country, as follows: Singapore (most prioritized), Malaysia, Indonesia.
How should I query it?
The result should be like this:
Sounds like you need an extra table to hold the priority value for countries. Then you could order by this value. Combine this with the ROW_NUMBER statement and recursive logic to get the results you require.
You will end up with something like this:
--Sample data
DECLARE @fruit AS TABLE ([Type] VARCHAR(50), Country VARCHAR(50), Price DECIMAL(5,2))
DECLARE @country_priority AS TABLE (Country VARCHAR(50), [Priority] INT)
INSERT INTO @country_priority VALUES ('Singapore',3)
INSERT INTO @country_priority VALUES ('Malaysia',2)
INSERT INTO @country_priority VALUES ('Indonesia',1)
INSERT INTO @fruit VALUES ('Orange','Malaysia',25)
INSERT INTO @fruit VALUES ('Orange','Singapore',30)
INSERT INTO @fruit VALUES ('Orange','Indonesia',28)
INSERT INTO @fruit VALUES ('Apple','Malaysia',65)
INSERT INTO @fruit VALUES ('Apple','Singapore',55)
INSERT INTO @fruit VALUES ('Banana','Indonesia',88)
INSERT INTO @fruit VALUES ('Durian','Malaysia',77)
INSERT INTO @fruit VALUES ('Pineapple','Indonesia',15)
--query
;WITH summary AS (
SELECT f.[Type],
f.Country,
f.Price,
ROW_NUMBER() OVER(PARTITION BY f.[Type]
ORDER BY cp.[Priority] DESC) AS rank
FROM @fruit f
INNER JOIN @country_priority cp
ON f.Country = cp.Country)
SELECT s.*
FROM summary s
WHERE s.rank = 1
--results
Type Country Price rank
Apple Singapore 55.00 1
Banana Indonesia 88.00 1
Durian Malaysia 77.00 1
Orange Singapore 30.00 1
Pineapple Indonesia 15.00 1
Note: The join above is base on a varchar column so I could get a quick example up for you to see. Please chose an appropriate type for your needs. Here is a good thread for you to check out.
