select name - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

select name

Author

Topic

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-08-24 : 07:06:28

select f.mobid, f.merchant, x.minprice, x.maxprice
from (
select mobid, min(price) as minprice, max(price) as maxprice
from tbl_merchant group by mobid
) as x inner join tbl_merchant as f on f.mobid = x.mobid and f.price = x.minprice order by mobid

This query selects min & max price i need that merchant name also

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-08-24 : 07:13:24

Please help me .......

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-24 : 07:23:32

your query looks fine. whats the problem then?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-08-24 : 07:26:36

this query selects min & max price values, merchant names from my table.... i need the name of that merchant also ...Who is min & whose max holder

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-08-24 : 07:34:45

My table contains merchant name in one field and price in another field ...
I already separated min & max price values from price field now i want the name of the merchant

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-24 : 07:37:57

ok here you go


select mobid, merchant,price
from
(
select row_number() over (order by price) as seq,row_number() over (order by price desc) as bseq,mobid, merchant,price
from tbl_merchant
)t
where seq=1
or bseq=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-08-24 : 07:42:02

I need my result as mobid|min_merchant|min_price|Max_merchant|max_price

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-08-24 : 07:43:45

Your query just returns mobid|merchant|price

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-08-24 : 07:44:32

To get my o/p how to modify my query

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-24 : 07:50:05

do you mean you've multiple merchants per mobid? then it should be


select mobid, 
max(case when seq=1 then merchant else null end) as minmerchant,
max(case when seq=1 then price else null end) as minprice,
max(case when bseq=1 then merchant else null end) as maxmerchant,
max(case when bseq=1 then price else null end) as maxprice
from
(
select row_number() over (partition by mobid order by price) as seq,row_number() over (partition by mobid order by price desc) as bseq,mobid, merchant,price
from tbl_merchant
)t
where seq=1
or bseq=1
group by mobid

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2010-08-24 : 07:56:49

Thanks a lot ..Its working...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-25 : 07:58:46

See this topic for continuation http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=149262

N 56°04'39.26"
E 12°55'05.63"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-25 : 09:13:46

quote:
Originally posted by jafrywilson

Thanks a lot ..Its working...

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources