SQL: How to select a record where the value is lowest per unique identifier

user_9118028 0
Viewed 77

I am new to SQL and have been trawling around for an answer on this for the past few days but can't seem to find a definitive answer. (Or I am not understanding the solutions given as they don't match my scenario)

I have a table that resembles the below:

unique reference|tel number| tel priority
123|0123456910|2
123|0654321910|6
214|0056897910|4

I want to only output data where the priority of the tel number is the lowest for each unique reference, so in the above example I would want:

unique reference|tel number| tel priority
123|0123456910|2
214|0056897910|4

Any pointers/guidance is much appreciated, I have tried the MIN() functions but was unable to get it to do this as an output so I think I am missing something.

(I have SQL server 2008 r2 or Microsoft report builder)

reportbuilder3.0 sql-server-2008-r2 sql-server sql
3 Answers

This gives the result you want: 

SELECT * FROM phones WHERE tel_priority = (SELECT MIN(tel_priority) FROM phones p WHERE p.unique_reference = phones.unique_reference)

assuming phones is the name of the table.
Of course if there are more than 1 rows containing the lowest value of tel_priority for a given unique_reference, then all these rows will be fetched.

user_104988280

You can use row_number() :

select t.*
from (select t.*, 
             row_number() over (partition by uniquereference order by priority) as seq
      from table t
     )
where seq = 1;

If the priority has ties then use dense_rank() instead of row_number().

user_55294450

Here is the simple solution. i have tested and its working fine.if you like the answer then please vote

 select * from tablename where tel_priority not in(select max(tel_priority) from tablename)
user_105757820
Related