Help needed writing this query

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

SQL Server 2000 Forums

Transact-SQL (2000)

Help needed writing this query

Author

Topic

hasanali00
Posting Yak Master

207 Posts

Posted - 2005-04-22 : 11:38:53

I wonder if u can help me with this query

I have 2 tables as:
Table1 (id, username, productID)
Products(productID, name...)

In Table1, i have data as


id   username  productid
1     user1      11
2     user1      11
3     user1      22
4     user1      22
5     user1      11
6     user1      33
7     user2      11

I want to do follwoing in my SP

(1) For each user in Table1, I want to return 2 MOST APPEARING productIDs. So for user1, I should get 11 and 22

Once I have got productIDs from Table1, I want to get Name of each product from Products table.

Any idea how I would do this.
thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-22 : 17:58:36

How about this?


set nocount on
declare @tb1 Table (id int, username varchar(10), productID int)
declare @tb2 table (productID int, productname varchar(10))

insert @tb1
select 1,     'user1',      11 union all
select 2,     'user1',      11 union all
select 3,     'user1',      22 union all
select 4,     'user1',      22 union all
select 5,     'user1',      11 union all
select 6,     'user1',      33 union all
select 7,     'user2',      11 

insert @tb2
select 11, 'product11' union all
select 22, 'product22' union all
select 33, 'product33'

select	distinct username, productname
from	@tb1 a
JOIN	@tb2 b ON a.productid = b.productid
Where	a.ProductID IN (
		Select top 2 productID 
		from @tb1 
		where username = a.username 
		group by productID
		order by count(*) desc
		)

Be One with the Optimizer
TG

hasanali00
Posting Yak Master

207 Posts

Posted - 2005-04-23 : 15:08:04

Thanks TG,
I tried your solution last night, but did not get the result i wanted , thats why I posted a new topic.

But I have tried your solution again. and it works perfectly well.

Would be grateful if u can explain to me how this query is actually working, specially this bit:
Where a.ProductID IN (
Select top 2 productID
from @tb1
where username = a.username
group by productID
order by count(*) desc
)

If I run the follwoing query WHY WOULDN'T I get the correct productid???
Select top 2 productID
from @tb1
where username = a.username
group by productID
order by count(*) desc

kind regards

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-23 : 16:29:51

I'm glad its working for you now, hopefully after the sun goes down again tonight it'll still work :)

The short answer is check out "Correlated Sub Queries" in Books on line.

The slightly longer answer is, the sub query is being executed once for each username (where username = a.username). So for each username the subquery is only returning the 2 productids that occur most often for the "current" user.

The last query you posted won't even run by itself because you're using a table alias (a) that's not defined. This however will behave the same as the sub-query but for just one user at a time:


Select top 2 productID 
from @tb1 
where username = 'user1'
group by productID
order by count(*) desc

Good luck!

Be One with the Optimizer
TG

hasanali00
Posting Yak Master

207 Posts

Posted - 2005-04-23 : 18:11:16

once again, thanks alot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources