MySQL for each single value of the column take at least 1 row up to 10 rows

Question

I have this output generated by a query LEFT JOIN in my DB MySQL:

mysql> SELECT
    LCD,
    zonOp,
    Codex,
    Q,
    Codex2,
    NumberCode,
    CodeDates
FROM
    tbl_A A
LEFT JOIN tbl_CB CB ON A.Codex = Codex2
WHERE
 (
    Q > 0
    AND Codex2 IS NOT NULL
    AND NumberCode IS NOT NULL
    AND CodeDates NOT IN ('0000-00-00')
)
AND zonOp = 'RSID'
GROUP BY
    LCD,
    NumberCode;
+--------+-------+-------+-------+--------+------------+------------+
| LCD    | zonOp | Codex | Q     | Codex2 | NumberCode | CodeDates  |
+--------+-------+-------+-------+--------+------------+------------+
| RSIDP0 | RSID  | XM7   | 0,024 | XM7    |   51015524 | 2014-06-18 |
| RSIDP1 | RSID  | XM7   | 1,544 | XM7    |   47266370 | 2014-06-18 |
| RSIDP2 | RSID  | XM7   | 0,835 | XM7    |   48742114 | 2014-06-13 |
| RSIDP3 | RSID  | XI6   | 0,13  | XI6    |   29721250 | 2014-06-11 |
| RSIDP3 | RSID  | XM7   | 0,302 | XM7    |   29721253 | 2013-12-20 |
| RSIDP3 | RSID  | XM7   | 0,687 | XM7    |   38680929 | 2014-06-18 |
| RSIDP3 | RSID  | XM7   | 0,795 | XM7    |   39415749 | 2014-06-16 |
| RSIDP3 | RSID  | XG6   | 0,006 | XG6    |   40240767 | 2014-06-16 |
| RSIDP3 | RSID  | XI6   | 0,09  | XI6    |   40240769 | 2014-06-17 |
| RSIDP3 | RSID  | XM7   | 0,056 | XM7    |   40240770 | 2014-06-16 |
| RSIDP3 | RSID  | XM7   | 0,504 | XM7    |   42624322 | 2014-06-16 |
| RSIDP3 | RSID  | XM7   | 0,516 | XM7    |   42797920 | 2014-06-17 |
| RSIDP3 | RSID  | XM7   | 0,61  | XM7    |   45180544 | 2014-06-17 |
| RSIDP3 | RSID  | XM7   | 0,249 | XM7    |   45494706 | 2014-06-16 |
| RSIDP3 | RSID  | XM7   | 0,252 | XM7    |   45541676 | 2014-06-16 |
| RSIDP3 | RSID  | XM7   | 0,033 | XM7    |   46309158 | 2014-06-16 |
| RSIDP3 | RSID  | XM7   | 0,303 | XM7    |   46348630 | 2014-06-25 |
| RSIDP3 | RSID  | XE5   | 0,068 | XE5    |   47542502 | 2014-06-17 |
| RSIDP3 | RSID  | XG6   | 0,07  | XG6    |   47542503 | 2014-06-17 |
| RSIDP3 | RSID  | XM7   | 0,231 | XM7    |   48541798 | 2014-06-16 |
| RSIDP3 | RSID  | XM7   | 0,178 | XM7    |   50230398 | 2013-12-20 |
+--------+-------+-------+-------+--------+------------+------------+
21 rows in set

Now I need to extract a query LEFT JOIN in my DB MySQL this new output.

The criteria is that for each single value of the column LCD take at least 1 row up to 10 rows:

+------+-------+------------+
|N_LCD | LCD   | NumberCode |
+------+-------+------------+
|1     |RSIDP0 |51015524    |
|1     |RSIDP1 |47266370    |
|1     |RSIDP2 |48742114    |
|1     |RSIDP3 |29721250    |
|2     |RSIDP3 |29721253    |
|3     |RSIDP3 |38680929    |
|4     |RSIDP3 |39415749    |
|5     |RSIDP3 |40240767    |
|6     |RSIDP3 |40240769    |
|7     |RSIDP3 |40240770    |
|8     |RSIDP3 |42624322    |
|9     |RSIDP3 |42797920    |
|10    |RSIDP3 |45180544    |
+------+-------+------------+

And I tried this solution:

mysql> SELECT
    LCD,
    zonOp,
    Codex,
    Q,
    Codex2,
    NumberCode,
    CodeDates
FROM
    tbl_A A
LEFT JOIN tbl_CB CB ON A.Codex = Codex2
WHERE
 (
    Q > 0
    AND Codex2 IS NOT NULL
    AND NumberCode IS NOT NULL
    AND CodeDates NOT IN ('0000-00-00')
)
AND zonOp = 'RSID'
GROUP BY
    LCD,
    NumberCode
ORDER BY
    RAND()
LIMIT 10;
+--------+-------+-------+-------+--------+------------+------------+
| LCD    | zonOp | Codex | Q     | Codex2 | NumberCode | CodeDates  |
+--------+-------+-------+-------+--------+------------+------------+
| RSIDP3 | RSID  | XM7   | 0,231 | XM7    |   48541798 | 2014-06-16 |
| RSIDP3 | RSID  | XE5   | 0,068 | XE5    |   47542502 | 2014-06-17 |
| RSIDP3 | RSID  | XM7   | 0,795 | XM7    |   39415749 | 2014-06-16 |
| RSIDP3 | RSID  | XM7   | 0,056 | XM7    |   40240770 | 2014-06-16 |
| RSIDP2 | RSID  | XM7   | 0,835 | XM7    |   48742114 | 2014-06-13 |
| RSIDP3 | RSID  | XM7   | 0,249 | XM7    |   45494706 | 2014-06-16 |
| RSIDP3 | RSID  | XI6   | 0,13  | XI6    |   29721250 | 2014-06-11 |
| RSIDP1 | RSID  | XM7   | 1,544 | XM7    |   47266370 | 2014-06-18 |
| RSIDP3 | RSID  | XM7   | 0,504 | XM7    |   42624322 | 2014-06-16 |
| RSIDP3 | RSID  | XM7   | 0,61  | XM7    |   45180544 | 2014-06-17 |
+--------+-------+-------+-------+--------+------------+------------+
10 rows in set

But the new output is wrong because for LCD value RSIDP0 I don't have output and for LCD value RSIDP3 I have 8 and not 10 rows.

Any help would be appreciated, thank you in advance.

My tables MySQL below.

DROP TABLE IF EXISTS `tbl_A`;
CREATE TABLE `tbl_A` (
  `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 `tbl_A` (`LCD`, `zonOp`, `Codex`, `Q`, `NumberCode`, `CodeDates`) VALUES ('RSIDP0', 'RSID', 'XM7', 0.02400, 51015524, '2014-6-18');
INSERT INTO `tbl_A` (`LCD`, `zonOp`, `Codex`, `Q`, `NumberCode`, `CodeDates`) VALUES ('RSIDP1', 'RSID', 'XM7', 1.54400, 47266370, '2014-6-18');
INSERT INTO `tbl_A` (`LCD`, `zonOp`, `Codex`, `Q`, `NumberCode`, `CodeDates`) VALUES ('RSIDP2', 'RSID', 'XM7', 0.83500, 48742114, '2014-6-13');
INSERT INTO `tbl_A` (`LCD`, `zonOp`, `Codex`, `Q`, `NumberCode`, `CodeDates`) VALUES ('RSIDP3', 'RSID', 'XI6', 0.13000, 29721250, '2014-6-11');
INSERT INTO `tbl_A` (`LCD`, `zonOp`, `Codex`, `Q`, `NumberCode`, `CodeDates`) VALUES ('RSIDP3', 'RSID', 'XM7', 0.30200, 29721253, '2013-12-20');
INSERT INTO `tbl_A` (`LCD`, `zonOp`, `Codex`, `Q`, `NumberCode`, `CodeDates`) VALUES ('RSIDP3', 'RSID', 'XM7', 0.68700, 38680929, '2014-6-18');
INSERT INTO `tbl_A` (`LCD`, `zonOp`, `Codex`, `Q`, `NumberCode`, `CodeDates`) VALUES ('RSIDP3', 'RSID', 'XM7', 0.79500, 39415749, '2014-6-16');
INSERT INTO `tbl_A` (`LCD`, `zonOp`, `Codex`, `Q`, `NumberCode`, `CodeDates`) VALUES ('RSIDP3', 'RSID', 'XG6', 0.00600, 40240767, '2014-6-16');
INSERT INTO `tbl_A` (`LCD`, `zonOp`, `Codex`, `Q`, `NumberCode`, `CodeDates`) VALUES ('RSIDP3', 'RSID', 'XI6', 0.09000, 40240769, '2014-6-17');
INSERT INTO `tbl_A` (`LCD`, `zonOp`, `Codex`, `Q`, `NumberCode`, `CodeDates`) VALUES ('RSIDP3', 'RSID', 'XM7', 0.05600, 40240770, '2014-6-16');
INSERT INTO `tbl_A` (`LCD`, `zonOp`, `Codex`, `Q`, `NumberCode`, `CodeDates`) VALUES ('RSIDP3', 'RSID', 'XM7', 0.50400, 42624322, '2014-6-16');
INSERT INTO `tbl_A` (`LCD`, `zonOp`, `Codex`, `Q`, `NumberCode`, `CodeDates`) VALUES ('RSIDP3', 'RSID', 'XM7', 0.51600, 42797920, '2014-6-17');
INSERT INTO `tbl_A` (`LCD`, `zonOp`, `Codex`, `Q`, `NumberCode`, `CodeDates`) VALUES ('RSIDP3', 'RSID', 'XM7', 0.61000, 45180544, '2014-6-17');
INSERT INTO `tbl_A` (`LCD`, `zonOp`, `Codex`, `Q`, `NumberCode`, `CodeDates`) VALUES ('RSIDP3', 'RSID', 'XM7', 0.24900, 45494706, '2014-6-16');
INSERT INTO `tbl_A` (`LCD`, `zonOp`, `Codex`, `Q`, `NumberCode`, `CodeDates`) VALUES ('RSIDP3', 'RSID', 'XM7', 0.25200, 45541676, '2014-6-16');
INSERT INTO `tbl_A` (`LCD`, `zonOp`, `Codex`, `Q`, `NumberCode`, `CodeDates`) VALUES ('RSIDP3', 'RSID', 'XM7', 0.03300, 46309158, '2014-6-16');
INSERT INTO `tbl_A` (`LCD`, `zonOp`, `Codex`, `Q`, `NumberCode`, `CodeDates`) VALUES ('RSIDP3', 'RSID', 'XM7', 0.30300, 46348630, '2014-6-25');
INSERT INTO `tbl_A` (`LCD`, `zonOp`, `Codex`, `Q`, `NumberCode`, `CodeDates`) VALUES ('RSIDP3', 'RSID', 'XE5', 0.06800, 47542502, '2014-6-17');
INSERT INTO `tbl_A` (`LCD`, `zonOp`, `Codex`, `Q`, `NumberCode`, `CodeDates`) VALUES ('RSIDP3', 'RSID', 'XG6', 0.07000, 47542503, '2014-6-17');
INSERT INTO `tbl_A` (`LCD`, `zonOp`, `Codex`, `Q`, `NumberCode`, `CodeDates`) VALUES ('RSIDP3', 'RSID', 'XM7', 0.23100, 48541798, '2014-6-16');
INSERT INTO `tbl_A` (`LCD`, `zonOp`, `Codex`, `Q`, `NumberCode`, `CodeDates`) VALUES ('RSIDP3', 'RSID', 'XM7', 0.17800, 50230398, '2013-12-20');



DROP TABLE IF EXISTS `tbl_cb`;
CREATE TABLE `tbl_cb` (
  `Codex2` varchar(3) NOT NULL,
  PRIMARY KEY (`Codex2`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `tbl_cb` VALUES ('XE5');
INSERT INTO `tbl_cb` VALUES ('XG6');
INSERT INTO `tbl_cb` VALUES ('XI6');
INSERT INTO `tbl_cb` VALUES ('XM7');
INSERT INTO `tbl_cb` VALUES ('XQ7');

I think you need something like SQL Server's [ranking function](http://stackoverflow.com/questions/12739208/how-to-use-rank-in-sql-server) for the `N_LCD` Column like `RANK() OVER(PARTITION BY (LCD) ORDER BY LCD, NumberCode)` along with `LIMIT` to limit the result to 10 rows. — user2989408, Aug 08 '14 at 17:02
thank you, but my database ys MySQL and not SQL server, i'm sorry. — Hamamelis, Aug 08 '14 at 17:16
I know that is why I did not post it as an answer. Check if [this](http://dba.stackexchange.com/questions/13703/get-the-rank-of-a-user-in-a-score-table) might help. Also here is an [SQL Fillde](http://sqlfiddle.com/#!3/7bdf2/3) with SQL Server query that generates your required output. — user2989408, Aug 08 '14 at 17:30
I have seen really powerful... but I don't know if there in MySQL..and the ouput in SQL Fillde is wrong 18 rows instead of 13... — Hamamelis, Aug 08 '14 at 18:07

MySQL for each single value of the column take at least 1 row up to 10 rows

0 Answers0