Need some help with SQL (group by and Select)

• taxali

  Newbie

  Points: 3

  More actions

  May 26, 2010 at 12:22 am

  #221600

  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!

• NewBeeSQL

  SSCrazy

  Points: 2170

  More actions

  May 26, 2010 at 1:06 am

  #1172493

  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;

  -----------------------------------------------------------------------
  For better assistance in answering your questions[/url]
  Perforamance Issues[/url]
  Cross Tabs and Pivots[/url]
  Cross Apply[/url]
  The Numbers or Tally Table- Jeff Moden[/url]