August 25, 2010 at 4:39 am
select t1.*,t2.Product_Name from
(
select mobid,
max(case when seq=1 then merchant else null end) as min_merchant,
max(case when seq=1 then price else null end) as min_price,
max(case when bseq=1 then merchant else null end) as max_merchant,
max(case when bseq=1 then price else null end) as max_price
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) and mobid in(select MOBID from tbl_product where Client_Name='wilson')
group by mobid
) as t1 INNER JOIN tbl_product as t2 on t2.mobid=t1.mobid
In the above query i need to add my_Price field where
my_price is where my name matches for that product
thanks in advance ....
August 25, 2010 at 4:53 am
Please post table definitions, sample data and expected results as per http://www.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 25, 2010 at 5:14 am
table 1
id mobid product description user name
1 aa1 xxxx xxxxxx qqq 111
2 aa1 xxxx xxxxxx www 111
3 aa1 xxxx xxxxxx eee 111
4 aa2 yyyy yyyyyy rrr 222
5 aa2 yyyy yyyyy qqq 222
6 aa3 zzzzz zzzzzz qqq 333
table 2
id mobid merchant price
1 aa1 wilson 10
2 aa1 jafry 11
3 aa1 abcd 8
4 aa2 wilson 5
5 aa2 jafry 4
i need my output like this( my name is wilson)...
name | description | my_price | lowprice_merchant | low_price | maxprice_merchant | max_price
111 xxxxx 10 abcd 8 jafry 11
August 25, 2010 at 5:43 am
I'm gonna guess that you didn't read the article I referenced. Please do.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 25, 2010 at 5:55 am
Asked with a lot of discussion and work done here : http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=149262
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply