Distinct / Order By / Group By??

I have a bunch of PhoneDeals in my vDeals view...

when i query this, i do something similar to:

select * from vDeals where HandsetID = '1234'

This returns a load of results.
A lot of them, have the same tariffID, but a different phonePrice.
what i want to do, is select only ONE row for each tariffID, ordered by the lowest price of the phone...

For example:

DealID | TariffID | HandsetID | PhonePrice |

1 | t1 | 1234 | 12.99
2 | t1 | 1234 | 102.44
3 | t1 | 1234 | 139.45
4 | t2 | 1234 | 23.52
5 | t2 | 1234 | 123.52
6 | t2 | 1234 | 2.52
7 | t3 | 1234 | 23.52




So from the data above, the returned results should be:

1 | t1 | 1234 | 12.99
6 | t2 | 1234 | 2.52
7 | t3 | 1234 | 23.52


how can i do that?

how about this:
select dealid, tariffid, handsetid, min(phoneprice) from vDeals where handsetid = '1234' group by dealid, tariffid, handsetid

Try thisWITH
CTE AS(select dealid, tariffid
, handsetid, phoneprice
,ROW_NUMBER
()OVER(PARTITION BY tariffid ORDER BY phoneprice ASC) AS seqfrom
vDeals where
handsetid = '1234' )SELECT dealid, tariffid
, handsetid, phonepriceFROM
CTE WHERE
seq=1

[quote user="alexjamesbrown"]
For example:

DealID | TariffID | HandsetID | PhonePrice |

1 | t1 | 1234 | 12.99
2 | t1 | 1234 | 102.44
3 | t1 | 1234 | 139.45
4 | t2 | 1234 | 23.52
5 | t2 | 1234 | 123.52
6 | t2 | 1234 | 2.52
7 | t3 | 1234 | 23.52


[/quote]
You can try any of the below
select min(DealID), TariffID, HandsetID, min(PhonePrice)
from test_table
group by TariffID, HandsetID
or
select max(DealID), TariffID, HandsetID, min(PhonePrice)
from test_table
group by TariffID, HandsetID

SELECT vD.*
FROM vDeals vD join (
SELECT TariffID, MIN(PhonePrice) AS minPrice FROM vDeals GROUP BY TariffID) as PminPrice
on vD.TariffID = PminPrice.TariffID and
vD. PhonePrice= PminPrice.minPrice
where vD.HandSetID = '1234'

Hi,

Pls try the below query...hope it will be useful for you to display the record which has minimum phone price based on Trafficid and display the phone price from the lowest value..</ br>

Select b.dealid,a.trafficid,b.Handsetid,a.phoneprice from tr b,(Select trafficid ,min(phoneprice)as Phoneprice from trgroup by trafficid,handsetid)a where a.trafficid = b.trafficid and a.phoneprice = b.phonepriceorder by b.phoneprice asc

Thanks & Regards

Sakthimeenakshi.S