Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941

    Unanswered: Select top 3 by group

    I need to find the first 3 days that a product is scheduled in one of my
    tables. It may be scheduled on 12 different days, but I only want the first 3.

    So, based on some sample data and this thread:

    http://www.dbforums.com/t1115304.html


    I was able to come up with a query that does what I need.
    However, I have no idea how it works. For one, what
    is Count(*) counting in the query?

    Code:
    create table #tmpSched (
    	sched_date  datetime,
    	product char(3)
    )
    
    insert into #tmpsched
    select '1/1/2001', 'abc'
    union all
    select '1/2/2001','abc'
    union all
    select '1/3/2001','abc'
    union all
    select '1/1/2001', 'def'
    union all 
    select '1/2/2001','def'
    union all
    select '1/1/2001', 'ghi'
    union all
    select '1/1/2001', 'jkl'
    union all
    select '1/2/2001','jkl'
    union all
    select '1/3/2001','jkl'
    union all
    select '1/4/2001','jkl'
    
    SELECT a.SCHED_DATE, a.PRODUCT
        FROM #tmpSched a 
        INNER JOIN #tmpSched b
          ON a.product  = b.product
          AND a.sched_date > = b.sched_date
    GROUP BY a.product, a.sched_date
      HAVING COUNT(*) <= 3
    order by a.product, a.sched_date
    
    drop table #tmpsched
    Any explanation would be appreciated.

    EDIT: Wanted first 3, not last 3. Changed "a.sched_date < = b.sched_date" to a.sched_date > = b.sched_date
    Last edited by RedNeckGeek; 02-21-07 at 15:48.
    Inspiration Through Fermentation

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Now I've got it to give me what I'm really looking for,
    but is this more complex than it needs to be?

    I needed to pivot the data, so that I can keep everything on one row.
    I came up with a "counter" to allow me to pivot into three fields, and then worked my way "out".

    Code:
    SELECT PRODUCT, 
    MAX(SCHED_DATE1) AS SCHED_DATE1, 
    MAX(SCHED_DATE2) AS SCHED_DATE2, 
    MAX(SCHED_DATE3) AS SCHED_DATE3
    FROM (
      SELECT PRODUCT, 
       'SCHED_DATE1'=CASE WHEN CNT=1 THEN SCHED_DATE END,
       'SCHED_DATE2'=CASE WHEN CNT=2 THEN SCHED_DATE END,
       'SCHED_DATE3'=CASE WHEN CNT=3 THEN SCHED_DATE END
      FROM (
         SELECT a.SCHED_DATE, 
                    a.PRODUCT, 
                    (SELECT COUNT(*) AS CNT 
                       FROM #TMPSCHED C 
                       WHERE C.SCHED_DATE<=A.SCHED_DATE 
                          AND C.PRODUCT=A.PRODUCT) AS CNT
         FROM #tmpSched a 
            INNER JOIN #tmpSched b
            ON a.product  = b.product
            AND a.sched_date > = b.sched_date
        GROUP BY a.product, a.sched_date
        HAVING COUNT(*) <= 3) D 
    ) E
    GROUP BY PRODUCT
    ORDER BY PRODUCT
    Inspiration Through Fermentation

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by RedNeckGeek
    Any explanation would be appreciated.
    i'd be happy to try, given that i'm the guilty party whose sql you used

    thanks for the trip down memory lane, by the way, that was a fun thread

    are you sure you want me to go into it though?

    i mean, since you have gone in a more sophisticated direction...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I think it must've come to me in a dream last night. It's pretty clear to me today. Still curious if you see any flaws in my query, though. I'd hate to
    replace one mistake with another.
    Inspiration Through Fermentation

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    flaws in your query? you mean, other than the needless complexity?

    the main criterion is, does it produce the correct result?

    here's another way --
    Code:
    SELECT a.PRODUCT
         , a.SCHED_DATE
      FROM #tmpSched a 
     WHERE ( select count(*)
               from #tmpSched 
              where product = a.product
                AND sched_date <= a.sched_date )
             <= 3
    ORDER
        BY a.PRODUCT
         , a.SCHED_DATE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by r937
    the main criterion is, does it produce the correct result?
    Pshaw! That's like saying the main criteria for a painting is that it cover a wall. A good piece of code is a work of art!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Well....
    My paint does cover the wall, but my question remains:
    Can it be done with less brushwork?

    I do have a way of "over-complexifying" things.
    Inspiration Through Fermentation

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hmmm... you have 3 queries to choose from... all purportedly work, but only one of which (the one that looks like salvador dali on acid painted it) puts all the data onto a single row...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    C'mon Rudy, throw me a bone.

    I'm guessing that you're saying I could clean it up a "little" bit.
    Inspiration Through Fermentation

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, that's not what i'm saying

    i'm saying if all three queries work (i.e. produce the correct result), then pick the one you like the best
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "I don't know coding, but I know what I like."
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    May 2005
    Posts
    48
    If you are using SQL Server 2005, the PIVOT function could be of help in reducing the code.

  13. #13
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Quote Originally Posted by SQLDBA_2005
    If you are using SQL Server 2005, the PIVOT function could be of help in reducing the code.
    No such luck. Still on SQL Server 1883

    Actually, right or wrong, this is now embedded deeply in my sproc, in
    a few different places. It seems to be working just fine.
    Inspiration Through Fermentation

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •