|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
/* 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 |