Click here to monitor SSC

Software Engineer - Red Gate Software

A Sql Puzzle for you all

Published 28 October 2005 2:10 pm
/*
To try and help everyone get a little better at sql I am setting a
little puzzle for you all to do based on a problem that Richard had
with the check for updates site.

The puzzle is simple. We have a version table that holds all the version
of all our products that we release. The version number is represented
with four numbers a,b,c and d where a is the major version and the other
letters represent which sub version it is. So the puzzle is to write a
query to find the latest build for each major version. So in result from
the data bellow should be.

a           b           c           d          
———– ———– ———– ———–
1           2           2           4
2           3           1           7
3           2           1           5

You are only alowed to use one SQL statment and definantly no cursers. Also
no solutions that use any strings!!!!!. ALSO a, b, c or d could all potentally be
Int32.MaxValue so make sure your solutions don’t overflow!!!!!!!

Email solutions to me.

Here is some example data to get you started
*/
DECLARE @Version TABLE
(
    a int,
    b int,
    c int,
    d int
)

INSERT INTO @Version(a, b, c, d)
    SELECT 1,1,1,23
INSERT INTO @Version(a, b, c, d)
    SELECT 1,2,1,1
INSERT INTO @Version(a, b, c, d)
    SELECT 1,2,2,4
INSERT INTO @Version(a, b, c, d)
    SELECT 2,1,1,1
INSERT INTO @Version(a, b, c, d)
    SELECT 3,1,1,2
INSERT INTO @Version(a, b, c, d)
    SELECT 2,1,2,8
INSERT INTO @Version(a, b, c, d)
    SELECT 2,1,3,4
INSERT INTO @Version(a, b, c, d)
    SELECT 2,2,1,2
INSERT INTO @Version(a, b, c, d)
    SELECT 2,2,3,1
INSERT INTO @Version(a, b, c, d)
    SELECT 2,2,4,6
INSERT INTO @Version(a, b, c, d)
    SELECT 2,3,1,7
INSERT INTO @Version(a, b, c, d)
    SELECT 3,2,1,5

9 Responses to “A Sql Puzzle for you all”

  1. Anonymous says:

    SELECT a, b,c,d from @version Ta
    where Ta.b = (select max(b) from @version Tb where Ta.a=Tb.a)
    and Ta.c = (select max(c) from @version Tc where Tc.a=Ta.a and Tc.b=ta.b)
    and Ta.d = (select max(d) from @version Td where Td.a=Ta.a and Td.b=ta.b and Td.c=Ta.c)

    select Ta.a, Ta.b, Ta.c, Ta.d
    from @Version Ta
    where not exists( SELECT 0 from @version Tb WHERE ta.a = Tb.a and ta.b < Tb.b)
    and not exists( SELECT 0 from @version Tc WHERE Ta.a = Tc.a and Ta.b = Tc.b AND Ta.c < Tc.c)
    and not exists( SELECT 0 from @version Td WHERE Ta.a = Td.a and Ta.b = Td.b AND Ta.c = Td.c AND Ta.d

  2. Lionel says:

    Both those look good. Do people think that the comments is a good place to post solutions? I have a number of good solutions for this particular puzzle from various people :)

    Lionel

  3. Anonymous says:

    I was wondering where the answers were going to be posted. Comments seems like as good a place as any.

  4. Anonymous says:

    According to exection planer, the one above that uses exists is far superior to the other solution. However, both of them beat my solution:

    SELECT A.a, B.b, C.c, D.d
    FROM #Version A JOIN (SELECT Sub1.a,
    Max(Sub1.b) AS b
    FROM #Version Sub1
    GROUP BY Sub1.a) B ON A.a = B.a
    JOIN (SELECT Sub2.a, Sub2.b,
    Max(Sub2.c) AS c
    FROM #Version Sub2
    GROUP BY Sub2.a,
    Sub2.b) C ON B.a = C.a AND B.b = C.b
    JOIN (SELECT Sub3.a, Sub3.b, Sub3.c,
    Max(Sub3.d) AS d
    FROM #Version Sub3
    GROUP BY Sub3.a,
    Sub3.b,
    Sub3.c) D ON C.a = D.a AND C.b = D.b AND C.c = D.c
    GROUP BY A.a, B.b, C.c, D.d

  5. Anonymous says:

    It turns out, if you put a composite key on a,b,c,d then mine and the “not exists” solution come up equal in execution planner.

    So, if you properly index your data then you can remain dumb and SQL server will figure out the best way for you. :)

  6. Celko says:

    Why make it so had?

    SELECT a, b, c, d
    FROM (SELECT a, b, c, d,
    ROW_NUMBER() OVER(PARTITION BY a ORDER BY b DESC, c DESC, d DESC) AS latest
    FROM Version) AS X(a, b, c, d, latest)
    WHERE latest = 1;

  7. AlexM says:

    Even less code:

    SELECT TOP 1 WITH TIES *
    FROM @version
    ORDER BY ROW_NUMBER() over (PARTITION BY a ORDER BY b DESC, c DESC, d DESC)

    (much like to the solution of Celko, but I came to it before reading comments)

  8. karteekgopu says:

    SELECT d.a,d.b,d.c, MAX(E.d)d FROM
    (SELECT b.a, b.b, MAX(C.C)c FROM
    (SELECT a, MAX(b)b FROM @Version
    GROUP BY a)b
    INNER JOIN @Version C ON b.a = c.a and b.b = c.b
    GROUP BY b.a, b.b)d
    INNER JOIN @Version e ON d.a = e.a and d.b = e.b and d.c = e.c
    GROUP BY d.a,d.b,d.c

Leave a Reply