StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow do I convert MySQL that uses group by and order by clauses to T-SQL query?
text
Body
copied!<p>I've got the following MySQL query that I would like to translate so it works in MSSQL:</p> <pre><code>SELECT * FROM ( SELECT * FROM ranking ORDER BY rank_no ASC , effective_dt DESC ) AS sorted_rank WHERE sorted_rank.rank_id = 1950 GROUP BY sorted_rank.rank_no LIMIT 10 </code></pre> <p>I've spent an afternoon fiddling but I keep running into problems with GROUP BY and aggregates and all kinds of other errors.</p> <p>To add a further spanner in the works, the ranking table DOES NOT have a primary key, and I think it may be required to get this working...</p> <p><em>Server Version</em>: <strong>Microsoft SQL Server 2000 - 8.00.2039</strong></p> <p><em>Schema</em>:</p> <pre><code>rank_id int(11) week_id int(11) rider_id int(11) year_no int(11) rank_no int(11) effective_dt datetime lastupdate_dt datetime point_no float average_no float result_qy int(11) </code></pre> <p><em>Dataset</em>:</p> <pre><code>INSERT INTO `ranking` (`id`, `rank_id`, `week_id`, `rider_id`, `year_no`, `rank_no`, `effective_dt`, `lastupdate_dt`, `point_no`, `average_no`, `result_qy`) VALUES (244, 1950, 417, 72253, 2007, 1, '2006-09-03 00:00:00', '2006-09-01 01:45:00', 2559.19, 426.53, 5), (108, 1950, 426, 72253, 2007, 1, '2006-11-05 00:00:00', '2006-11-01 05:59:00', 2559.19, 426.53, 5), (340, 1950, 386, 21767, 2006, 1, '2006-01-29 00:00:00', '2006-11-29 13:31:00', 3256.25, 814.06, 4), (178, 1950, 420, 60369, 2007, 2, '2006-09-24 00:00:00', '2006-09-26 06:31:00', 2315.86, 385.98, 4), (166, 1950, 417, 60369, 2007, 2, '2006-09-03 00:00:00', '2006-09-01 01:45:00', 2315.86, 385.98, 4), (109, 1950, 426, 60369, 2007, 2, '2006-11-05 00:00:00', '2006-11-01 05:59:00', 2315.86, 385.98, 4), (110, 1950, 426, 49428, 2007, 3, '2006-11-05 00:00:00', '2006-11-01 05:59:00', 2191.19, 365.2, 4), (227, 1950, 417, 49428, 2007, 3, '2006-09-03 00:00:00', '2006-09-01 01:45:00', 2191.19, 365.2, 4), (409, 1950, 388, 19570, 2006, 3, '2006-02-12 00:00:00', '2006-11-29 13:31:00', 3106.26, 776.57, 4), (72, 1950, 399, 47036, 2006, 4, '2006-04-30 00:00:00', '2006-11-29 13:33:00', 1038.02, 346.01, 3), (413, 1950, 388, 55533, 2006, 4, '2006-02-12 00:00:00', '2006-11-29 13:31:00', 2835.3, 708.83, 4), (111, 1950, 426, 64517, 2007, 4, '2006-11-05 00:00:00', '2006-11-01 05:59:00', 2001.68, 333.61, 4), (112, 1950, 426, 72379, 2007, 5, '2006-11-05 00:00:00', '2006-11-01 05:59:00', 1677.32, 279.55, 6), (263, 1950, 420, 27123, 2007, 5, '2006-09-24 00:00:00', '2006-09-26 06:31:00', 1607.31, 267.88, 4), (415, 1950, 388, 45738, 2006, 5, '2006-02-12 00:00:00', '2006-11-29 13:31:00', 2744.03, 686.01, 4), (113, 1950, 426, 27123, 2007, 6, '2006-11-05 00:00:00', '2006-11-01 05:59:00', 1607.31, 267.88, 4), (575, 1950, 420, 50354, 2007, 6, '2006-09-24 00:00:00', '2006-09-26 06:31:00', 1148.84, 191.47, 2), (422, 1950, 388, 39070, 2006, 6, '2006-02-12 00:00:00', '2006-11-29 13:31:00', 2626.51, 656.63, 4), (114, 1950, 426, 65745, 2007, 7, '2006-11-05 00:00:00', '2006-11-01 05:59:00', 1576.83, 262.8, 4), (293, 1950, 388, 42127, 2006, 7, '2006-02-12 00:00:00', '2006-11-29 13:31:00', 2614.46, 653.62, 4), (576, 1950, 420, 32669, 2007, 7, '2006-09-24 00:00:00', '2006-09-26 06:31:00', 1133.15, 188.86, 2), (577, 1950, 420, 23242, 2007, 8, '2006-09-24 00:00:00', '2006-09-26 06:31:00', 1107.79, 184.63, 2), (199, 1950, 420, 60322, 2007, 8, '2006-09-24 00:00:00', '2006-09-26 06:31:00', 1390.22, 231.7, 3), (115, 1950, 426, 54984, 2007, 8, '2006-11-05 00:00:00', '2006-11-01 05:59:00', 1532.8, 255.47, 3), (578, 1950, 420, 41116, 2007, 9, '2006-09-24 00:00:00', '2006-09-26 06:31:00', 1096.64, 182.77, 2), (116, 1950, 426, 72386, 2007, 9, '2006-11-05 00:00:00', '2006-11-01 05:59:00', 1442.49, 240.41, 6), (74, 1950, 399, 32669, 2006, 9, '2006-04-30 00:00:00', '2006-11-29 13:33:00', 817.64, 272.55, 3), (818890, 1950, 1167, 77510, 0, 10, '2012-01-13 00:00:00', '2012-01-13 11:11:00', 1464.43, 366.11, 4), (825706, 1950, 1168, 70324, 0, 10, '2012-01-20 00:00:00', '2012-01-20 11:05:00', 1259.38, 314.85, 2), (826752, 1950, 1170, 75911, 0, 10, '2012-02-01 00:00:00', '2012-02-01 12:58:00', 1237.95, 309.49, 3); </code></pre> <p><em>Correct output</em>:</p> <pre><code>1950 426 72253 2007 1 2006-11-05 00:00:00 2006-11-01 05:59:00 2559.19 426.53 5 1950 426 60369 2007 2 2006-11-05 00:00:00 2006-11-01 05:59:00 2315.86 385.98 4 1950 426 49428 2007 3 2006-11-05 00:00:00 2006-11-01 05:59:00 2191.19 365.2 4 1950 426 64517 2007 4 2006-11-05 00:00:00 2006-11-01 05:59:00 2001.68 333.61 4 1950 426 72379 2007 5 2006-11-05 00:00:00 2006-11-01 05:59:00 1677.32 279.55 6 1950 426 27123 2007 6 2006-11-05 00:00:00 2006-11-01 05:59:00 1607.31 267.88 4 1950 426 65745 2007 7 2006-11-05 00:00:00 2006-11-01 05:59:00 1576.83 262.8 4 1950 426 54984 2007 8 2006-11-05 00:00:00 2006-11-01 05:59:00 1532.8 255.47 3 1950 426 72386 2007 9 2006-11-05 00:00:00 2006-11-01 05:59:00 1442.49 240.41 6 1950 1170 75911 0 10 2012-02-01 00:00:00 2012-02-01 12:58:00 1237.95 309.49 3 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload