Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Unanswered: A coding challenge from the blindman.

    Hey it's not often the blindman asks for advice on sql coding (never, I think), so here is an opportunity to solve a problem I've been knocking my head against for two days.

    Here is sample code for setting up the problem:
    Code:
    create table #blindman
    	(pkey smallint primary key,
    	fkey char(1),
    	updated	datetime)
    
    insert into #blindman (pkey, fkey, updated)
    select	1, 'A', '1/1/2006'
    UNION
    select	2, 'B', '1/1/2006'
    UNION
    select	3, 'A', '1/2/2006'
    UNION
    select	4, 'B', '1/2/2006'
    UNION
    select	5, 'A', '1/4/2006'
    UNION
    select	6, 'B', '1/2/2006'
    UNION
    select	7, 'A', '1/3/2006'
    UNION
    select	8, 'B', '1/3/2006'
    UNION
    select	9, 'A', '1/5/2006'
    UNION
    select	10, 'B', '1/5/2006'
    
    drop table #blindman
    Notice that for fkey 'B', there are two entries with '1/2/2006', and for fkey 'A' the updated values are not in synch with the order of the primary key.
    The challenge: determine the next pkey for each pkey value, ordered by [updated], and using pkey as a tie-breaker when two records have the same [updated] value.
    Here is the desired output for the sample data:
    Code:
    pkey   fkey updated    nextpkey
    ------ ---- ---------- --------
         1 A    2006-01-01        3
         3 A    2006-01-02        7
         7 A    2006-01-03        5
         5 A    2006-01-04        9
         2 B    2006-01-01        4
         4 B    2006-01-02        6
         6 B    2006-01-02        8
         8 B    2006-01-03       10
    Records 9 and 10 are missing because they have not succeeding records, though I'd be just has satisfied to include them with NULL as their nextpkey value.
    Ideally, I want this as a VIEW.

    Who's up for the challenge?
    If it's not practically useful, then it's practically useless.

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

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Where's the challange?

    Code:
        SELECT * 
          FROM #blindman a 
     LEFT JOIN #blindman b 
    	ON a.fkey = b.fkey 
           AND b.pkey = (SELECT MIN(c.pkey) 
    		       FROM #blindman c 
    		      WHERE c.pkey > a.pkey
    			AND c.fkey = a.fkey
    		   GROUP BY c.fkey )
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Here's a quick and dirty solution that works. I'm not in your leaque, so I'm sure you can clean it up:
    Code:
    CREATE PROCEDURE [dbo].[procBlindman] AS
    
    SELECT pkey, fkey, updated, IDENTITY(int, 1,1) AS Sequence
    INTO #temp
    FROM blindman Order BY fkey, updated
    
    SELECT *, 
    (SELECT pkey FROM #temp AS Alias2 WHERE Sequence = (SELECT Min(Sequence) FROM #temp AS Alias
      WHERE Alias.Sequence > #temp.Sequence
        AND Alias.fkey = #temp.fkey)) AS NextPkey
     FROM #temp
    GO
    Paul

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Does SQL2000 vs SQL 2005 matter? OR can you use bells and whistles from either?

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Brett, your solution does not give the correct output. It gives this:
    Code:
    pkey   fkey updated                                                nextpkey 
    ------ ---- ------------------------------------------------------ -------- 
         1 A    2006-01-01 00:00:00.000                                       3 
         3 A    2006-01-02 00:00:00.000                                       5 
         7 A    2006-01-03 00:00:00.000                                       9 
         5 A    2006-01-04 00:00:00.000                                       7 
         9 A    2006-01-05 00:00:00.000                                NULL
         2 B    2006-01-01 00:00:00.000                                       4 
         4 B    2006-01-02 00:00:00.000                                       6 
         6 B    2006-01-02 00:00:00.000                                       8 
         8 B    2006-01-03 00:00:00.000                                      10 
        10 B    2006-01-05 00:00:00.000                                NULL
    The nextpkey for fkey 'A' should be in the order 3, 7, 5, 9. Your solution has record 3 being followed by record 5, when record seven is the next to follow.

    pbaldy, your solution may be the one I have to go with, either using a temp table or a hideously large subquery to assign ordinal value, but I'd rather avoid the first method because I want this as a view, and the second method I fear will be very slow.
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Dude,

    I'm hurt and dismayed

    All you need is an ORDER BY

    Code:
        SELECT a.pkey, a.fkey, a.updated, b.pkey 
          FROM #blindman a 
     LEFT JOIN #blindman b 
    	ON a.fkey = b.fkey 
           AND b.pkey = (SELECT MIN(c.pkey) 
    		       FROM #blindman c 
    		      WHERE c.pkey > a.pkey
    			AND c.fkey = a.fkey
    		   GROUP BY c.fkey )
    ORDER BY a.fkey, a.updated
    This Returns
    Code:
    pkey   fkey updated                                                pkey   
    ------ ---- ------------------------------------------------------ ------ 
    1      A    2006-01-01 00:00:00.000                                3
    3      A    2006-01-02 00:00:00.000                                5
    7      A    2006-01-03 00:00:00.000                                9
    5      A    2006-01-04 00:00:00.000                                7
    9      A    2006-01-05 00:00:00.000                                NULL
    2      B    2006-01-01 00:00:00.000                                4
    4      B    2006-01-02 00:00:00.000                                6
    6      B    2006-01-02 00:00:00.000                                8
    8      B    2006-01-03 00:00:00.000                                10
    10     B    2006-01-05 00:00:00.000                                NULL
    
    (10 row(s) affected)

    [code]
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Desired output:
    Code:
    pkey   fkey updated    nextpkey
    ------ ---- ---------- --------
         1 A    2006-01-01        3
         3 A    2006-01-02        7
         7 A    2006-01-03        5
         5 A    2006-01-04        9
    Brett's output
    Code:
    pkey   fkey updated                                                pkey   
    ------ ---- ------------------------------------------------------ ------ 
    1      A    2006-01-01 00:00:00.000                                3
    3      A    2006-01-02 00:00:00.000                                5
    7      A    2006-01-03 00:00:00.000                                9
    5      A    2006-01-04 00:00:00.000                                7
    One of these things is not like the other.
    One of these things is kinda-the-same.
    One of these things is not like the other.
    Can you tell me now before I finish my song?
    If it's not practically useful, then it's practically useless.

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

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK, so you want it by date...the comparison then just shift to look at that...

    Let me fire up QA...but it's really not that big of a change
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Does this float your boat?
    Code:
    SELECT a.pkey, a.fkey, a.updated, Coalesce(Min(b.pkey), Min(c.pkey)) AS nextpkey
       FROM #blindman AS a
       LEFT JOIN #blindman AS b
          ON (b.fkey = a.fkey
          AND b.updated = a.updated
          AND a.pkey < b.pkey)
       LEFT JOIN #blindman AS c
          ON (c.fkey = a.fkey
          AND c.updated = (SELECT Min(d.updated)
             FROM #blindman AS d
             WHERE  d.fkey = a.fkey
                AND a.updated < d.updated))
       GROUP BY a.fkey, a.updated, a.pkey
       ORDER BY a.fkey, a.updated, a.pkey
    -PatP

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    A double outer-join with coalesce. Very creative. Definitely an "outside the box" solution. I will try applying it to my actual problem.

    Thanks.
    If it's not practically useful, then it's practically useless.

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

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As I suspected, there was a better solution (the plan scales much better for large result sets):
    Code:
    SELECT a.pkey, a.fkey, a.updated, Coalesce(
       (SELECT Min(b.pkey)
          FROM #blindman AS b
          WHERE  b.fkey = a.fkey
             AND b.updated = a.updated
             AND a.pkey < b.pkey)
    , (SELECT Min(c.pkey)
          FROM #blindman AS c
          WHERE  c.fkey = a.fkey
             AND c.updated = (SELECT Min(d.updated)
                FROM #blindman AS d
                WHERE  d.fkey = a.fkey
                   AND a.updated < d.updated))) AS nextpkey
       FROM #blindman AS a
       ORDER BY a.fkey, a.updated, a.pkey
    -PatP

  12. #12
    Join Date
    Jun 2003
    Posts
    269
    I missed the challenge(actually I tried today,didnt got the solution).Anyway congratulation Pat.U deserve a
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK, and the reason for this is?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Its a homework assignment.



    Actually, the example given is a simplified version of a view I need to develop for a project.
    If it's not practically useful, then it's practically useless.

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

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    sounds like you need some table normalization
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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