How to Select Last ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to Select Last ?

Author

Topic

Prestidigitator
Starting Member

16 Posts

Posted - 2007-01-25 : 13:14:37

Is there synatax that would allow you to do a Select * on a table but qualify it to just return the Last row or the last X rows?

While add it is there a syntax to return just the first X rows found?

Been hunting in the reference documentation but not finding this.

Thanks!

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2007-01-25 : 13:19:49

[code]
SELECT TOP x * FROM Table ORDER BY someField <ASC/DESC>
[/code]

Prestidigitator
Starting Member

16 Posts

Posted - 2007-01-25 : 13:21:29

Ok for the top, what about to get just the last record?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2007-01-25 : 13:38:57

order by DESC


-- get most recent sale
SELECT top 1 *
FROM sales
ORDER by Saledate DESC

Prestidigitator
Starting Member

16 Posts

Posted - 2007-01-25 : 13:48:01

That makes sense.

How come the top X isn't working. I tried couple tests in query analyser and got the following errors:

quote:
select TOP 10 * from claim
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '10'.

SELECT top 1 *
FROM claim
ORDER by claim_id DESC
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '1'.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2007-01-25 : 13:55:45

that ought to work. is there anything else in the query analyzer editor window? if so, highlight just the query u are trying to execute and hit F5. what version of sql server are u running?

Prestidigitator
Starting Member

16 Posts

Posted - 2007-01-25 : 14:03:58

I am not sure which version, how do I find out? I am pretty sure it is a current version as it is in a production setting. I do see it is running MS SQL Server Service Manager version 8.00.2039

I did make sure I only highlighted one command at a time. It doesn't like that syntax for some reason.

Prestidigitator
Starting Member

16 Posts

Posted - 2007-01-25 : 14:53:27

Some techies internally explained the problem to me. Apparently for backwards compatability our databases are set to compatability level 65 AND it turns out the biggest issue of syntax is that TOP wasn't supported at that level.

So That explains why I am getting no where with the syntax you gave me. When I changed it to compatability of 70 it runs fine.

Who would have thunk!

Thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources