#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Location
    Vancouver, WA
    Posts
    4
    Rep Power
    0

    Finding TOP 1 of TOP 2


    I am trying to find the second most popular item color. I am able with the inner query find the TOP 2, but I have not been able to figure out how to formulate the outer query to isolate the second entry. Any suggestions?


    SELECT TOP 1 itemcolor
    FROM qitem
    WHERE itemcolor IN (

    SELECT TOP 2 itemcolor, SUM(saleqty)
    FROM qitem, qsale
    WHERE qitem.itemname=qsale.itemname
    and itemcolor <> '-'
    GROUP BY itemcolor
    order by SUM(saleqty)desc)
    Group By itemcolor
    order by itemcolor asc
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,385
    Rep Power
    391
    Code:
    SELECT TOP 1 itemcolor
    FROM (
    SELECT TOP 2 itemcolor, 
    SUM(saleqty) as x
    FROM qitem, qsale
    WHERE qitem.itemname=qsale.itemname
    and itemcolor <> '-'
    GROUP BY itemcolor
    order by x desc) dt
    order by x asc
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Location
    Vancouver, WA
    Posts
    4
    Rep Power
    0
    Thanks swampBoogie, just what I needed...

IMN logo majestic logo threadwatch logo seochat tools logo