Skip to content

Instantly share code, notes, and snippets.

@umidjons
Created November 27, 2013 13:24
Show Gist options
  • Save umidjons/7675613 to your computer and use it in GitHub Desktop.
Save umidjons/7675613 to your computer and use it in GitHub Desktop.
MySQL: Row number per group
SELECT
t.`code`,
t.title,
t.price_total,
(
CASE `code`
WHEN @curCode THEN
@curRow := @curRow + 1
ELSE
@curRow := 1
AND @curCode := `code`
END
) AS sort_by_total
FROM
`_products_out_2013_11_27_17_25_00` t,
(
SELECT
@curRow := 0,
@curCode := ''
) rt
ORDER BY
title,
`code`,
price_total
/*
OUTPUT:
------------------
CODE TITLE PRICE_TOTAL SORT_BY_TOTAL
1000374 Mitsubishi Electric MSZ-GE35VA / MUZ-GE35VA 36900 1
1000374 Mitsubishi Electric MSZ-GE35VA / MUZ-GE35VA 38230 2
1000374 Mitsubishi Electric MSZ-GE35VA / MUZ-GE35VA 38800 3
1000374 Mitsubishi Electric MSZ-GE35VA / MUZ-GE35VA 42100 4
1000374 Mitsubishi Electric MSZ-GE35VA / MUZ-GE35VA 43476 5
1000605 Mitsubishi Electric MSC-GE20VB-E1 / MU-GA20VB-E1 16800 1
1000605 Mitsubishi Electric MSC-GE20VB-E1 / MU-GA20VB-E1 16900 2
1000605 Mitsubishi Electric MSC-GE20VB-E1 / MU-GA20VB-E1 19052 3
1000605 Mitsubishi Electric MSC-GE20VB-E1 / MU-GA20VB-E1 19300 4
1000605 Mitsubishi Electric MSC-GE20VB-E1 / MU-GA20VB-E1 21200 5
1000606 Mitsubishi Electric MSC-GE25VB-E1 / MU-GA25VB-E1 20045 1
1000606 Mitsubishi Electric MSC-GE25VB-E1 / MU-GA25VB-E1 22200 2
1000606 Mitsubishi Electric MSC-GE25VB-E1 / MU-GA25VB-E1 22402 3
1000606 Mitsubishi Electric MSC-GE25VB-E1 / MU-GA25VB-E1 23590 4
1000606 Mitsubishi Electric MSC-GE25VB-E1 / MU-GA25VB-E1 25200 5
1000611 Mitsubishi Electric MSC-GE20VB-E1 / MUH-GA20VB-E1 21420 1
1000611 Mitsubishi Electric MSC-GE20VB-E1 / MUH-GA20VB-E1 22590 2
1000611 Mitsubishi Electric MSC-GE20VB-E1 / MUH-GA20VB-E1 23212 3
1000611 Mitsubishi Electric MSC-GE20VB-E1 / MUH-GA20VB-E1 24609 4
1000611 Mitsubishi Electric MSC-GE20VB-E1 / MUH-GA20VB-E1 25300 5
1000612 Mitsubishi Electric MSC-GE25VB-E1 / MUH-GA25VB-E1 21890 1
1000612 Mitsubishi Electric MSC-GE25VB-E1 / MUH-GA25VB-E1 25010 2
1000612 Mitsubishi Electric MSC-GE25VB-E1 / MUH-GA25VB-E1 25590 3
1000612 Mitsubishi Electric MSC-GE25VB-E1 / MUH-GA25VB-E1 27156 4
1000612 Mitsubishi Electric MSC-GE25VB-E1 / MUH-GA25VB-E1 27200 5
1000613 Mitsubishi Electric MSC-GE35VB-E1 / MUH-GA35VB-E1 27500 1
1000613 Mitsubishi Electric MSC-GE35VB-E1 / MUH-GA35VB-E1 30590 2
1000613 Mitsubishi Electric MSC-GE35VB-E1 / MUH-GA35VB-E1 31209 3
1000613 Mitsubishi Electric MSC-GE35VB-E1 / MUH-GA35VB-E1 32698 4
1000613 Mitsubishi Electric MSC-GE35VB-E1 / MUH-GA35VB-E1 34200 5
1000621 Mitsubishi Electric MSZ-HC35VA / MUZ-HC35VA 28070 1
1000621 Mitsubishi Electric MSZ-HC35VA / MUZ-HC35VA 28790 2
1000621 Mitsubishi Electric MSZ-HC35VA / MUZ-HC35VA 32234 3
1000621 Mitsubishi Electric MSZ-HC35VA / MUZ-HC35VA 33200 4
1000621 Mitsubishi Electric MSZ-HC35VA / MUZ-HC35VA 34500 5
1000637 Mitsubishi Electric MSZ-FD35VA / MUZ-FD35VA 41700 1
1000637 Mitsubishi Electric MSZ-FD35VA / MUZ-FD35VA 45800 2
1000637 Mitsubishi Electric MSZ-FD35VA / MUZ-FD35VA 45800 3
1000637 Mitsubishi Electric MSZ-FD35VA / MUZ-FD35VA 46367 4
1000637 Mitsubishi Electric MSZ-FD35VA / MUZ-FD35VA 48100 5
5003715 Mitsubishi Electric MSZ-FH25VE / MUZ-FH25VEHZ 48600 1
5003715 Mitsubishi Electric MSZ-FH25VE / MUZ-FH25VEHZ 49700 2
5003715 Mitsubishi Electric MSZ-FH25VE / MUZ-FH25VEHZ 50656 3
5003715 Mitsubishi Electric MSZ-FH25VE / MUZ-FH25VEHZ 50790 4
5003715 Mitsubishi Electric MSZ-FH25VE / MUZ-FH25VEHZ 63015 5
5003716 Mitsubishi Electric MSZ-FH35VE / MUZ-FH35VEHZ 62000 1
5003716 Mitsubishi Electric MSZ-FH35VE / MUZ-FH35VEHZ 62166 2
5003716 Mitsubishi Electric MSZ-FH35VE / MUZ-FH35VEHZ 62390 3
5003716 Mitsubishi Electric MSZ-FH35VE / MUZ-FH35VEHZ 70200 4
5003716 Mitsubishi Electric MSZ-FH35VE / MUZ-FH35VEHZ 78007 5
5003717 Mitsubishi Electric MSZ-FH50VE / MUZ-FH50VEHZ 77865 1
5003717 Mitsubishi Electric MSZ-FH50VE / MUZ-FH50VEHZ 85500 2
5003717 Mitsubishi Electric MSZ-FH50VE / MUZ-FH50VEHZ 103000 3
5003717 Mitsubishi Electric MSZ-FH50VE / MUZ-FH50VEHZ 103490 4
5003717 Mitsubishi Electric MSZ-FH50VE / MUZ-FH50VEHZ 110931 5
*/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment