28 October 2005

A Sql Puzzle for you all

/*
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

Keep up to date with Simple-Talk

For more articles like this delivered fortnightly, sign up to the Simple-Talk newsletter

This post has been viewed 2601 times – thanks for reading.

  • Rate
    [Total: 0    Average: 0/5]
  • Share