locked
MySQL use Limit RRS feed

  • Question

  • User-908043281 posted

    Hi all.

    I have a problem with mysql database.

    I have this table with 122 rows and columns `OrderTco` and `OrderCode`.

    `OrderTco` is the geographical area of origin of an order, `OrderCode` is a code of order that for the same geographical area can be repeated.

    I need to extract from this table maximum 10 lines for single geographical area.

    I need this output in which for area A1 extract all rows because I have 9 rows, for area B1 and area C1 the first 10 rows:

    +-----+----------+-----------+
    | ID  | OrderTco | OrderCode |
    +-----+----------+-----------+
    |   1 | A1       |  47421842 |
    |   2 | A1       |  48062487 |
    |   3 | A1       |  48889676 |
    |   4 | A1       |  50384199 |
    |   5 | A1       |  50887328 |
    |   6 | A1       |  50918700 |
    |   7 | A1       |  51248220 |
    |   8 | A1       |  51794088 |
    |   9 | A1       |  90817292 |
    |  10 | B1       |  41771826 |
    |  11 | B1       |  44723830 |
    |  12 | B1       |  45483247 |
    |  13 | B1       |  46148815 |
    |  14 | B1       |  46514834 |
    |  15 | B1       |  46834766 |
    |  16 | B1       |  46834770 |
    |  17 | B1       |  47615351 |
    |  18 | B1       |  47615653 |
    |  19 | B1       |  47802697 |
    |  60 | C1       |  42156626 |
    |  61 | C1       |  46156395 |
    |  62 | C1       |  47099964 |
    |  63 | C1       |  47737295 |
    |  64 | C1       |  48672260 |
    |  65 | C1       |  49048345 |
    |  66 | C1       |  49370148 |
    |  67 | C1       |  50781626 |
    |  68 | C1       |  51580145 |
    |  69 | C1       |  51580146 |
    +-----+----------+-----------+

    I thought of a grouping by `OrderTco`, `OrderCode` and Limit 10 but I can not have the output.

    My code below.

    Any help would be appreciated, thanks in advance.

    DROP TABLE IF EXISTS `dotable`;
    CREATE TABLE `dotable` (
      `ID` int(11) NOT NULL AUTO_INCREMENT,
      `OrderTco` char(6) DEFAULT NULL,
      `OrderCode` int(11) DEFAULT NULL,
      PRIMARY KEY (`ID`)
    ) ENGINE=MyISAM AUTO_INCREMENT=123 DEFAULT CHARSET=latin1;
    
    
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('1', 'A1', '47421842');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('2', 'A1', '48062487');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('3', 'A1', '48889676');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('4', 'A1', '50384199');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('5', 'A1', '50887328');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('6', 'A1', '50918700');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('7', 'A1', '51248220');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('8', 'A1', '51794088');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('9', 'A1', '90817292');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('10', 'B1', '41771826');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('11', 'B1', '44723830');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('12', 'B1', '45483247');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('13', 'B1', '46148815');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('14', 'B1', '46514834');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('15', 'B1', '46834766');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('16', 'B1', '46834770');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('17', 'B1', '47615351');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('18', 'B1', '47615653');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('19', 'B1', '47802697');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('20', 'B1', '48069101');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('21', 'B1', '48215307');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('22', 'B1', '48287092');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('23', 'B1', '48361996');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('24', 'B1', '48610861');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('25', 'B1', '48881052');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('26', 'B1', '48990004');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('27', 'B1', '49170385');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('28', 'B1', '49810778');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('29', 'B1', '49864962');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('30', 'B1', '50093254');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('31', 'B1', '50150294');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('32', 'B1', '50151510');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('33', 'B1', '50669799');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('34', 'B1', '50948841');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('35', 'B1', '51096692');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('36', 'B1', '51096793');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('37', 'B1', '51848609');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('38', 'B1', '52018758');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('39', 'B1', '52129746');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('40', 'B1', '89318652');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('41', 'B1', '89969871');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('42', 'B1', '89969874');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('43', 'B1', '89969885');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('44', 'B1', '89969887');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('45', 'B1', '89984300');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('46', 'B1', '90642243');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('47', 'B1', '90642246');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('48', 'B1', '90656056');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('49', 'B1', '90681042');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('50', 'B1', '90686901');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('51', 'B1', '90687088');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('52', 'B1', '90694120');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('53', 'B1', '90771866');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('54', 'B1', '90829027');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('55', 'B1', '90866489');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('56', 'B1', '90938371');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('57', 'B1', '90949259');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('58', 'B1', '91125485');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('59', 'B1', '91174802');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('60', 'C1', '42156626');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('61', 'C1', '46156395');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('62', 'C1', '47099964');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('63', 'C1', '47737295');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('64', 'C1', '48672260');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('65', 'C1', '49048345');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('66', 'C1', '49370148');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('67', 'C1', '50781626');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('68', 'C1', '51580145');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('69', 'C1', '51580146');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('70', 'C1', '51590131');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('71', 'C1', '51593663');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('72', 'C1', '52110407');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('73', 'C1', '88959568');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('74', 'C1', '89107949');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('75', 'C1', '89559925');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('76', 'C1', '89822330');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('77', 'C1', '89853401');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('78', 'C1', '89875109');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('79', 'C1', '90143788');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('80', 'C1', '90147121');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('81', 'C1', '90261952');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('82', 'C1', '90262117');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('83', 'C1', '90268972');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('84', 'C1', '90349287');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('85', 'C1', '90350763');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('86', 'C1', '90354055');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('87', 'C1', '90388795');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('88', 'C1', '90424846');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('89', 'C1', '90498839');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('90', 'C1', '90503670');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('91', 'C1', '90508353');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('92', 'C1', '90518025');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('93', 'C1', '90520214');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('94', 'C1', '90546077');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('95', 'C1', '90546078');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('96', 'C1', '90554790');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('97', 'C1', '90554800');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('98', 'C1', '90565102');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('99', 'C1', '90571253');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('100', 'C1', '90631905');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('101', 'C1', '90631971');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('102', 'C1', '90656093');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('103', 'C1', '90660564');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('104', 'C1', '90669061');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('105', 'C1', '90669309');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('106', 'C1', '90685269');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('107', 'C1', '90687702');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('108', 'C1', '90767056');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('109', 'C1', '90767745');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('110', 'C1', '90768541');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('111', 'C1', '90902399');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('112', 'C1', '90902528');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('113', 'C1', '90908367');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('114', 'C1', '90908789');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('115', 'C1', '90929476');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('116', 'C1', '90929477');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('117', 'C1', '90929718');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('118', 'C1', '91038039');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('119', 'C1', '91056217');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('120', 'C1', '91056340');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('121', 'C1', '91087005');
    INSERT INTO `dotable` (OrderTco, OrderCode) VALUES ('122', 'C1', '91139612');

    Thursday, August 21, 2014 7:57 AM

Answers

  • User1558924997 posted
    SET @tid=NULL, @oid=NULL;
    SELECT id, OrderTco, OrderCode, rowNum FROM (
    	SELECT  IF(orderTCO=@oid, @tid:=@tid+1, @tid:=1) rowNum,  id, OrderTco, OrderCode ,  @oid:=orderTCO ass2
    	FROM dotable
    ) a
    WHERE rowNum<=10;



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 21, 2014 9:37 AM

All replies

  • User1558924997 posted
    SET @tid=NULL, @oid=NULL;
    SELECT id, OrderTco, OrderCode, rowNum FROM (
    	SELECT  IF(orderTCO=@oid, @tid:=@tid+1, @tid:=1) rowNum,  id, OrderTco, OrderCode ,  @oid:=orderTCO ass2
    	FROM dotable
    ) a
    WHERE rowNum<=10;



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 21, 2014 9:37 AM
  • User1428246847 posted

    http://dev.mysql.com/doc/refman/5.0/en/select.html Wink

    select * from yourtable limit offset, count

    offset is the first record that you want to retrieve (starting from 0) and count is the number of rows. If the number of rows is larger than the available number of rows (e.g 19 rows and you use 10,10) it will only return the last 9.

    //Edit:

    It's highly advised to use an order by clause to get a consistent output.

    Thursday, August 21, 2014 10:28 AM