retrieving last row inserted

[joeidee]

Id Product Quantity Amount Date
1 Shoe 4 200 2011-08-27
2 Shoe 2 100 2011-08-28
3 Cap 2 50 2011-08-28
4 Shirt 1 300 2011-08-28
5 Cap 1 25 2011-08-29
6 Shoe 1 50 2011-08-29
7 Shirt 2 600 2011-08-29

This my database structure. what I want to know is how to get the quantity value for the last inserted row of Cap before today which is 2011-08-29.
I need a query to solve this problem for me.
thanks in advance
joe

[sv3tli0]

SELECT * FROM TABLENAME ORDER BY id DESC LIMIT 1

ORDER BY id DESC is ordering all row by ID starting from last ( DESC means in back order, normally its ASC)
LIMIT 1 is givving us only the first record (in our case the last one because its starting to check it from the bottom with this DESC)

[vibhaJ]

I think you need group by.
check this http://vibhajadwani.wordpress.com/2011/08/03/left-join-with-limit/

[eXpertPHP]

Table:

CREATE TABLE IF NOT EXISTS `table_name` (
  `Id` int(3) unsigned NOT NULL AUTO_INCREMENT,
  `Product` enum('Shoe','Cap','Shirt') NOT NULL,
  `Quantity` int(3) unsigned NOT NULL,
  `Amount` float NOT NULL,
  `Date` date NOT NULL,
  PRIMARY KEY (`Id`),
  KEY `Product` (`Product`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8;

Rows:

INSERT INTO `table_name` (`Id`, `Product`, `Quantity`, `Amount`, `Date`) VALUES
(1, 'Shoe', 4, 200, '2011-08-27'),
(2, 'Shoe', 2, 100, '2011-08-28'),
(3, 'Cap', 2, 50, '2011-08-28'),
(4, 'Shirt', 1, 300, '2011-08-28'),
(5, 'Cap', 1, 25, '2011-08-29'),
(6, 'Shoe', 1, 50, '2011-08-29'),
(7, 'Shirt', 2, 600, '2011-08-29');

Query:

SELECT `Quantity` FROM `table_name` WHERE `Product` = 'Cap' AND `Date` <> CURDATE() ORDER BY Date DESC LIMIT 1

[joeidee]

thanks all the solutions you all have given have help in solving my problem.