Created
November 27, 2013 13:24
-
-
Save umidjons/7675613 to your computer and use it in GitHub Desktop.
MySQL: Row number per group
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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