3

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;
2
  • Can you provide your CREATE TABLE ? you may want to use rownumber for this
    – Racer SQL
    Jun 19, 2015 at 11:19
  • @RafaelPiccinelli, I edited above question
    – Sush
    Jun 19, 2015 at 11:22

1 Answer 1

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

12
  • TOP is not working in Mysql
    – Sush
    Jun 19, 2015 at 11:24
  • You should use LIMIT with mysql. it's the same of TOP.
    – Racer SQL
    Jun 19, 2015 at 11:25
  • No No it's fine,I want in Mysql
    – Sush
    Jun 19, 2015 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. Jun 19, 2015 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, 2015 at 12:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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