May 26, 2010 at 12:22 am
Hi,
I have an SQL question that I need an answer to.
Assume 2 tables:
Table 1 : "CDs" that holds 'CDName' and 'CD_ID' [P.K.]
Table 2 : "CDOrders" that holds 'DateOrderPlaced', 'CD_ID_Ordered' [F.K.]
CDs
CD_ID CDName
1 cd-a-name
2 cd-b-name
3 cd-c-name
4 cd-d-name
CDOrders
DateOrderPlaced CD_ID_Ordered
23May10 1
23May10 2
22May10 4
23May10 1
24May10 3
The purpose of the DB is to track each order placed for each CD. So above, on the 23 May, 'cd-a-name' was ordered the most (twice).
SQL Question: From all orders placed on a given day (say today), find out the names of the top 5 most-ordered CDs.
I got so far:
select top 5
CD_ID_Ordered, count(0)
from
CDOrders
where
<..DateOrderPlaced is today.. >
group by
CD_ID_Ordered
order by
count(0) desc;
This gives me the CD_IDs in the order I want (1st most ordered, 2nd most ordered...5th most). But I need to retrieve the CDName from the CDs table, and show that as the result of the query, not its corresponding ID.
Can anyone please help ?
Thanks!
May 26, 2010 at 1:06 am
Check out this..
create table #CDS(CDID int,CDName varchar(50))
insert into #CDS select 1,'cd-a'
union all
select 2,'cd-b'
union all
select 3,'cd-c'
union all
select 4,'cd-d'
create table #CDOrders(CDOrdereddate datetime,CDid int)
insert into #CDOrders select getdate(),1
union all
select getdate(),1
union all
select getdate(),1
union all
select getdate()-1,2
select top 5
a.CDOrdereddate, cdname,count(b.cdid)
from
#CDOrders a inner join
#CDS b on a.cdid = b.cdid
group by
CDOrdereddate,b.cdname
order by
count(b.cdid) desc;
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply