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 this

 

WITH CTE AS

(

select dealid,

tariffid,

handsetid,

phoneprice,

ROW_NUMBER()OVER(PARTITION BY tariffid ORDER BY phoneprice ASC) AS seq

from vDeals

where handsetid = '1234'

)

SELECT dealid,

tariffid,

handsetid,

phoneprice

FROM CTE

WHERE seq=1

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

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..

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

Thanks & Regards

Sakthimeenakshi.S

Refer point 3 too

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

sakthi.tnj:

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..

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

Thanks & Regards

Sakthimeenakshi.S

 

I think you can avoid subquery in this case