Rick James Wrote:
-------------------------------------------------------
> The subquery that does the ranking needs an ORDER
> BY clause.
thank you, you mean this?
mysql> DROP TABLE
IF EXISTS `test`;
CREATE TABLE `test` (
`LCD` CHAR (6) DEFAULT NULL,
`NumberCode` INT (11) DEFAULT NULL,
`CodeDates` date DEFAULT NULL,
`Codex` CHAR (10) DEFAULT NULL,
`Q` DECIMAL (10, 5) DEFAULT NULL,
`zonOp` CHAR (4) DEFAULT NULL,
`ID` INT (11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`ID`)
) ENGINE = MyISAM DEFAULT CHARSET = latin1;
INSERT INTO `test` (
`LCD`,
`zonOp`,
`Codex`,
`Q`,
`NumberCode`,
`CodeDates`
)
VALUES
(
'RSIDP0',
'RSID',
'XM7',
0.02400,
51015524,
'2014-6-18'
);
INSERT INTO `test` (
`LCD`,
`zonOp`,
`Codex`,
`Q`,
`NumberCode`,
`CodeDates`
)
VALUES
(
'RSIDP1',
'RSID',
'XM7',
1.54400,
47266370,
'2014-6-18'
);
INSERT INTO `test` (
`LCD`,
`zonOp`,
`Codex`,
`Q`,
`NumberCode`,
`CodeDates`
)
VALUES
(
'RSIDP2',
'RSID',
'XM7',
0.83500,
48742114,
'2014-6-13'
);
INSERT INTO `test` (
`LCD`,
`zonOp`,
`Codex`,
`Q`,
`NumberCode`,
`CodeDates`
)
VALUES
(
'RSIDP3',
'RSID',
'XI6',
0.13000,
29721250,
'2014-6-11'
);
INSERT INTO `test` (
`LCD`,
`zonOp`,
`Codex`,
`Q`,
`NumberCode`,
`CodeDates`
)
VALUES
(
'RSIDP3',
'RSID',
'XM7',
0.30200,
29721253,
'2013-12-20'
);
INSERT INTO `test` (
`LCD`,
`zonOp`,
`Codex`,
`Q`,
`NumberCode`,
`CodeDates`
)
VALUES
(
'RSIDP3',
'RSID',
'XM7',
0.68700,
38680929,
'2014-6-18'
);
INSERT INTO `test` (
`LCD`,
`zonOp`,
`Codex`,
`Q`,
`NumberCode`,
`CodeDates`
)
VALUES
(
'RSIDP3',
'RSID',
'XM7',
0.79500,
39415749,
'2014-6-16'
);
INSERT INTO `test` (
`LCD`,
`zonOp`,
`Codex`,
`Q`,
`NumberCode`,
`CodeDates`
)
VALUES
(
'RSIDP3',
'RSID',
'XG6',
0.00600,
40240767,
'2014-6-16'
);
INSERT INTO `test` (
`LCD`,
`zonOp`,
`Codex`,
`Q`,
`NumberCode`,
`CodeDates`
)
VALUES
(
'RSIDP3',
'RSID',
'XI6',
0.09000,
40240769,
'2014-6-17'
);
INSERT INTO `test` (
`LCD`,
`zonOp`,
`Codex`,
`Q`,
`NumberCode`,
`CodeDates`
)
VALUES
(
'RSIDP3',
'RSID',
'XM7',
0.05600,
40240770,
'2014-6-16'
);
INSERT INTO `test` (
`LCD`,
`zonOp`,
`Codex`,
`Q`,
`NumberCode`,
`CodeDates`
)
VALUES
(
'RSIDP3',
'RSID',
'XM7',
0.50400,
42624322,
'2014-6-16'
);
INSERT INTO `test` (
`LCD`,
`zonOp`,
`Codex`,
`Q`,
`NumberCode`,
`CodeDates`
)
VALUES
(
'RSIDP3',
'RSID',
'XM7',
0.51600,
42797920,
'2014-6-17'
);
INSERT INTO `test` (
`LCD`,
`zonOp`,
`Codex`,
`Q`,
`NumberCode`,
`CodeDates`
)
VALUES
(
'RSIDP3',
'RSID',
'XM7',
0.61000,
45180544,
'2014-6-17'
);
INSERT INTO `test` (
`LCD`,
`zonOp`,
`Codex`,
`Q`,
`NumberCode`,
`CodeDates`
)
VALUES
(
'RSIDP3',
'RSID',
'XM7',
0.24900,
45494706,
'2014-6-16'
);
INSERT INTO `test` (
`LCD`,
`zonOp`,
`Codex`,
`Q`,
`NumberCode`,
`CodeDates`
)
VALUES
(
'RSIDP3',
'RSID',
'XM7',
0.25200,
45541676,
'2014-6-16'
);
INSERT INTO `test` (
`LCD`,
`zonOp`,
`Codex`,
`Q`,
`NumberCode`,
`CodeDates`
)
VALUES
(
'RSIDP3',
'RSID',
'XM7',
0.03300,
46309158,
'2014-6-16'
);
INSERT INTO `test` (
`LCD`,
`zonOp`,
`Codex`,
`Q`,
`NumberCode`,
`CodeDates`
)
VALUES
(
'RSIDP3',
'RSID',
'XM7',
0.30300,
46348630,
'2014-6-25'
);
INSERT INTO `test` (
`LCD`,
`zonOp`,
`Codex`,
`Q`,
`NumberCode`,
`CodeDates`
)
VALUES
(
'RSIDP3',
'RSID',
'XE5',
0.06800,
47542502,
'2014-6-17'
);
INSERT INTO `test` (
`LCD`,
`zonOp`,
`Codex`,
`Q`,
`NumberCode`,
`CodeDates`
)
VALUES
(
'RSIDP5',
'RSID',
'123',
0.06800,
47542502,
'2014-6-17'
);
INSERT INTO `test` (
`LCD`,
`zonOp`,
`Codex`,
`Q`,
`NumberCode`,
`CodeDates`
)
VALUES
(
'RSIDP6',
'RSID',
'123',
0.06800,
47542502,
'2014-6-17'
);
INSERT INTO `test` (
`LCD`,
`zonOp`,
`Codex`,
`Q`,
`NumberCode`,
`CodeDates`
)
VALUES
(
'RSIDP7',
'RSID',
'XXX',
0.06800,
47542502,
'2014-6-17'
);
SELECT
tmp.LCD,
tmp.NUMBERCODE,
tmp.codex,
tmp.rank
FROM
(
SELECT
LCD,
NUMBERCODE,
codex,
IF (
@prev <> LCD,
@rownum := 1,
@rownum := @rownum + 1
) AS rank,
@prev := LCD
FROM
test t
JOIN (
SELECT
@rownum := NULL,
@prev := 0
) AS r
WHERE
(
Q > 0
AND Codex IS NOT NULL
AND NumberCode IS NOT NULL
AND CodeDates NOT IN ('0000-00-00')
)
AND zonOp = 'RSID'
ORDER BY
LCD,
NUMBERCODE
) AS tmp
JOIN tbl_CB CB ON tmp.Codex = cb.Codex2
WHERE
tmp.rank <= 10
GROUP BY
LCD,
NumberCode
ORDER BY
LCD,
NUMBERCODE;
+--------+------------+-------+------+
| LCD | NUMBERCODE | codex | rank |
+--------+------------+-------+------+
| RSIDP0 | 51015524 | XM7 | 1 |
| RSIDP1 | 47266370 | XM7 | 1 |
| RSIDP2 | 48742114 | XM7 | 1 |
| RSIDP3 | 29721250 | XI6 | 1 |
| RSIDP3 | 29721253 | XM7 | 2 |
| RSIDP3 | 38680929 | XM7 | 3 |
| RSIDP3 | 39415749 | XM7 | 4 |
| RSIDP3 | 40240767 | XG6 | 5 |
| RSIDP3 | 40240769 | XI6 | 6 |
| RSIDP3 | 40240770 | XM7 | 7 |
| RSIDP3 | 42624322 | XM7 | 8 |
| RSIDP3 | 42797920 | XM7 | 9 |
| RSIDP3 | 45180544 | XM7 | 10 |
+--------+------------+-------+------+
13 rows in set
mysql>