SQL Help

Wee · 6 Feb 2007 at 17:12

Working on some web stuff at the moment, and have come across a problem. I have a table in my database that is choco-block full of rows, but only want to display the 5 latest entries instead of them all. I was wondering it there is an SQL statement that is able to only output a specific number of rows?

happytechie · 6 Feb 2007 at 17:16

yes.

How are they indexed? if they are by a incrementing key number then order by that and get the last 5 records.

what columns are in the table and what database engine is it?

Haircut · 6 Feb 2007 at 17:24

Need to know what database, Oracle is quite different from most of the others with respect to getting the first n of a query

Wee · 6 Feb 2007 at 18:36

The database is SQL Server 2005.

They won't be indexed by an incremented key number, most likely will be done by date.

JonB · 6 Feb 2007 at 18:58

Something like:

SELECT * FROM <TABLENAME> ORDER BY <COLUMN NAME> DESC LIMIT 5

Wee · 6 Feb 2007 at 19:52

JonB said:
Something like:

SELECT * FROM <TABLENAME> ORDER BY <COLUMN NAME> DESC LIMIT 5

Will give it a try, cheers.

arty · 6 Feb 2007 at 20:20

I'd have thought it'd be a SELECT TOP 5 * FROM <TABLENAME> ORDER BY <COLUMN NAME> DESC if it's MS SQL Server

arty

unwashed potato! · 6 Feb 2007 at 21:01

JonB said:
Something like:

SELECT * FROM <TABLENAME> ORDER BY <COLUMN NAME> DESC LIMIT 5

but wont that order the column names in alphabetcial order instead of the latest 5 added?

JonB · 6 Feb 2007 at 21:10

Not if the column is in the date format....

JonB · 6 Feb 2007 at 21:11

arty said:
I'd have thought it'd be a SELECT TOP 5 * FROM <TABLENAME> ORDER BY <COLUMN NAME> DESC if it's MS SQL Server

arty

Both work. MySql doesn't like the "TOP" bit though.

Domo · 6 Feb 2007 at 21:14

arty said:
I'd have thought it'd be a SELECT TOP 5 * FROM <TABLENAME> ORDER BY <COLUMN NAME> DESC if it's MS SQL Server

arty

Statement I'd use

JonB · 6 Feb 2007 at 21:21

.....But yeah use his. It is more 'correct' than mine (even though both will work). I don't have a MSSQL server to test it on at home though!

Wee · 7 Feb 2007 at 08:23

arty said:
I'd have thought it'd be a SELECT TOP 5 * FROM <TABLENAME> ORDER BY <COLUMN NAME> DESC if it's MS SQL Server

arty

Appreciate the help folks, it worked a treat!

happytechie · 7 Feb 2007 at 09:15

just be aware that the TOP 5 syntax is not part of standard SQL so if you ever come to port your app to another database engine this statement will have ti change

HT