Hopefully simple SQL question 4

[TheVampire]

I'm not as up on SQL as some other things. Is there a SQL command to return the last X number of records in a database? Or am I forced to use regular commands to do a movelast, check the record #, and then jump back X number of records? For speed reasons, I wanted to avoid having to open a dynamic recordset.

Thanks,

Robert

 

[MarkSweetland]

Select top [X] ColumnName from TableName order by ColumnName desc

Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[DrJavaJoe]

Select Top X SomeField
From Table
Order by SomeField Desc

 

[vb5prgrmr]

try something like...
[tt]
sSQLVariable = "SELECT TOP " & YOUR_NUMBER_OF_RECORDS_THAT_YOU_WANT & " * FROM TABLE_NAME (OPTIONAL WHERE CRITERIA) ORDER BY AUTO_INCREMENT_FIELD DESC
[/tt]

so that would be...
[tt]
sSQLVariable = "SELECT TOP 10 * FROM TABLE1 ORDER BY AutoNum DESC"
[/tt]

Good Luck

 

[TheVampire]

Boy, you guys are quick! Less than 30 seconds and it's answered!

Robert

 

[TheVampire]

Hmm. Well, in practice this did not work as well as I had hoped. I really don't have a field that I can order it by, and if I just use "SELECT TOP 100 * FROM TABLENAME" they don't always come out in the order that they were added to the database ( all records are added consecutively ). Can you order by record #, even though that's not a "field"?

Thanks,

Robert

 

[BogdanMBM]

In your table ad a column in witch you put a TimeStamp value for each INSERT statement. Then you will be able to order DESC by thic column. See Books Online about TimeStanp values (data types).
Then you can use
Select Top X SomeField
From Table
Order by TimeStampColumn Desc

against your table

 

[TheVampire]

If I had a timestamp, which I don't. I'm dealing with an existing Database, not one that I am creating.

Thanks anyways.

Robert

 

[petevick]

If you leave out ORDER BY blah blah all together, ie "SELECT TOP 10 * FROM TABLE1" , won't this return your records in the order they appear in the db, ie, the order they were entered??

Pete Vickerstaff - Hedra Software

http://www.hedrasoftware.co.uk

 

[DugzDMan]

That all depends on the database your pulling against.

What are the fields you are pulling? What database are you pulling from?

 

[TheVampire]

I thought that it would do this, but apparantly it does not. If I look at the DB with a viewer program, the last 100 records that were added to the DB are in order, but when I use the SELECT TOP command, they don't get pulled out in that order. I am getting the last 100 entries, but they are not in the order that they were added to the DB.

I'm pulling out both of the fields in the table, which are a User ID # "USERID" and a string description of an action they performed "USERACTION".

Robert

 

[strongm]

I'm quite impressed that this thread has 4 stars when it appears that the problem hasn't been solved...

 

[TheVampire]

strongm,

Well, It had appeared that it would work back when I first posted, and if I had a field that I could sort on the posts that are starred would have worked, I suppose.

I guess that I am just going to go back to opening a dynamic recordset, skipping to the EOF and then going back 100 records from there and getting what I need. I was just trying to do it faster and with less code than that by using the SQL command.

Robert

 

[DugzDMan]

What type of DB are you pulling from? Some dbs insert data by hashing it and placing it in order of the has. I'm not sure if any of them use the date/time as a part of the hash they create. If so, there might (not sure) be a way to pull that info to at least create the initial order. From here on out I would suggest using some kind of counter, date/time stamp, ... to know what order they were inserted.

Just a thought. Good luck!

 

[TheVampire]

It's just a plain access database. If I'd designed it, I would have put a time/date in it. I've got to work with what I've got, though.

Thanks for the suggestions,

Robert

 

[DugzDMan]

Since it's Access, you might try to put in an auto number field. Maybe it will create the auto number field in the order that they were inserted???

 

[TheVampire]

That should be worth a try. I'll let you know it it works.

Robert

 

[chiph]

TheVampire -

As others have stated, without an ORDER BY clause, SQL will return rows in whatever order it feels best. This is part of the spec, BTW. Recall that SQL works on sets, not rows (think Venn diagrams).

Items in a set do not naturally have any order implied. If you were to use MS-SQLServer and it's Query Analyzer tool, you would see that in the execution plan, the ORDER BY gets done as one of the last steps before returning the data to you. Before that point, everything is orderless.

Chip H.


If you want to get the best response to a question, please check out FAQ222-2244 first