Syntax question

[me@randhm.co.uk]

Hello, please can someone advise me on the syntax to pull a penultimate record from a recorset. I can get my last record no problem by using the ORDER By newsID DESC but I also need to pull out the penultimate (last but one) record in a seperate recordset.I am new to SQl and so would really appreciate any help or pointer.ThanksGary

[pulpfiction]

Try this... it will get the last but one record.

SELECT * FROM tblName WHERE (newsID =  (SELECT TOP 1 newsID FROM tblName ORDER BY newsID DESC) - 1)

[me@randhm.co.uk]

Perfect thank you. Exactly what was required. I appreciate your help. A star you are

[Yahweh]

Try this... it will get the last but one record.

SELECT * FROM tblName WHERE (newsID =  (SELECT TOP 1 newsID FROM tblName ORDER BY newsID DESC) - 1)

That doesn't work if records have been deleted. For example:

ID--1234567

User deletes record 6:

ID--123457

When users run your query, they'll get an empty set.I suggest this query:

SELECT TOP (1) *FROM table1WHERE id in	(		Select TOP (2) id FROM table1 ORDER BY id DESC	)ORDER BY ID ASC

MySQL equivalent:

SELECT * from table1 ORDER BY ID DESC Limit 2, 1

[nibe49]

The beauty of Yahweh's code is that it can be easily adapted for last but 2, last but 3 etc.Here is a kludge for last but 1 which should work on anything - as long as your key is unique.SELECT * FROM tblNameWHERE newsID =(SELECT MAX(newsid) FROM tblNameWHERE newsID NOT IN (SELECT MAX(newsID)FROM tblName))regardsnibe

[pulpfiction]

That doesn't work if records have been deleted. For example:...........

Never thought of that situation. thanks for correcting.

[henryhenry]

This is from the mysql manual...LAST_INSERT_ID()LAST_INSERT_ID(expr)Returns the last automatically generated value that was inserted into an AUTO_INCREMENT column.

mysql> SELECT LAST_INSERT_ID();		-> 195

eg:

mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1);mysql> SELECT LAST_INSERT_ID();

Maybe it can also help? Once you have the insert_id you can easily update, select or whatever