2

Table Name :- ModelParam_EMS

Primary Key :- ParameterID

No of Rows :- 500

want to select 499th row from table only.

Note :- No. of rows can be less or more but not fixed.

Create Table :-

            CREATE TABLE `modelparam_ems` (
              `MakerModelID` smallint(5) unsigned NOT NULL,
              `ParameterID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
              `Category` tinyint(3) NOT NULL DEFAULT '1',
              `ParameterName` varchar(50) NOT NULL,
              `ParameterAdd` smallint(5) NOT NULL,
              `ParamterType` tinyint(3) unsigned NOT NULL,
              `DataTypeIndex` tinyint(3) unsigned NOT NULL,
              `DefaultResolutionIndex` tinyint(3) unsigned NOT NULL,
              `ParamUnit` varchar(20) NOT NULL,
              `ParamMaxVal` varchar(40) NOT NULL,
              `ParamMinVal` varchar(40) NOT NULL,
              `ParamVal` varchar(40) NOT NULL,
              `CreatedByID` smallint(5) unsigned DEFAULT NULL,
              `CreatedOn` datetime DEFAULT NULL,
              `ModifyById` smallint(5) unsigned DEFAULT NULL,
              `ModifyOn` datetime DEFAULT NULL,
              `chanelno` int(11) DEFAULT NULL,
              PRIMARY KEY (`ParameterID`),
              KEY `MkrMdlId` (`MakerModelID`),
              KEY `DTIndex` (`DataTypeIndex`),
              KEY `ResIndex` (`DefaultResolutionIndex`),
              KEY `CrtByID` (`CreatedByID`),
              KEY `ModfyById` (`ModifyById`),
              KEY `paramTp` (`ParamterType`),
              KEY `Categor` (`Category`),
              KEY `idx_modelparam_ems_parameterid` (`ParameterID`),
              CONSTRAINT `Category` FOREIGN KEY (`Category`) REFERENCES `category_ems` (`categoryid`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `m2m3` FOREIGN KEY (`MakerModelID`) REFERENCES `makermodel_ems` (`MakerModelID`) ON DELETE CASCADE ON UPDATE CASCADE,
              CONSTRAINT `modelparam_ems_ibfk_1` FOREIGN KEY (`CreatedByID`) REFERENCES `userlogin_ems` (`UserID`) ON DELETE SET NULL ON UPDATE SET NULL,
              CONSTRAINT `modelparam_ems_ibfk_2` FOREIGN KEY (`ModifyById`) REFERENCES `userlogin_ems` (`UserID`) ON DELETE SET NULL ON UPDATE SET NULL
            ) ENGINE=InnoDB AUTO_INCREMENT=10012 DEFAULT CHARSET=utf8;
  • Can you provide your CREATE TABLE ? you may want to use rownumber for this – Racer SQL Jun 19 '15 at 11:19
  • @RafaelPiccinelli, I edited above question – Sush Jun 19 '15 at 11:22
6

You can use this:

SELECT *
FROM table_name
ORDER BY column_name DESC
LIMIT n - 1, 1

The query just returns the first row after n-1 row(s)

For example, if you want to get the second most expensive product (n = 2) in the products table, you just use the following query:

SELECT productCode, productName, buyPrice
FROM products 
ORDER BY buyPrice desc 
LIMIT 1, 1

Try this. Could test here and it's ok.

Found this site helpfull

  • TOP is not working in Mysql – Sush Jun 19 '15 at 11:24
  • You should use LIMIT with mysql. it's the same of TOP. – Racer SQL Jun 19 '15 at 11:25
  • No No it's fine,I want in Mysql – Sush Jun 19 '15 at 11:25
  • 1
    Yes, this is good. I prefer the verbose but more clear LIMIT 1 OFFSET x syntax over of the obfuscating LIMIT x,1. – ypercubeᵀᴹ Jun 19 '15 at 12:34
  • Would SELECT * FROM table_name ORDER BY column_name -- note no "DESC" LIMIT 1, 1 not be better as it would use the natural order of the index. Could you give times for both? – Vérace Jun 19 '15 at 12:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.