Ammad Ammad - 7 months ago 35
SQL Question

How to get a particular values on priority basis

My table is as follows.

Table Values

ID Values
---- ------
A1 ---- 3
A2 ---- 3
A3 ---- 3
A4 ---- 3
A5 ---- 5
A6 ---- 6
A7 ---- 6


Table Priority

ID ------ Priority
A3 ----- 1
A2 ----- 2
A1 ----- 3
A5 ----- 4
A6 ----- 5
A4 ----- 6
A7 ----- 7


I want to find the lowest 2 value ID based on the priority for values less than 5.
If one or more ID has the same values then use the priority in the priority table and get the top 2 values from them.
As for the above case A1, A2, A3, and A4 has the same values and is less than 5. Then it should follow the priority given in priority table and fetches the top 2 records. For the above case it should show

ID ---- Values
A3 ---- 3
A2 ---- 3

Answer
SELECT TOP 2 tv.ID, tv.Values 
FROM table_values tv
INNER JOIN table_priorities tp
ON tv.Id = ip.ID
WHERE tv.values < 5
ORDER BY tv.values, tp.priority