LIMIT syntax in SQL Server
I am using SQL Server 6.5 and have come across the need to use something like MySQL's LIMIT syntax. Does SQL Server have anything comparable? Is there any other way to achieve this?
Gil Hildebrand, Jr.
Internet Consultant
New Orleans, LA
Mark Hensler posted this at 04:23 — 13th February 2001.
He has: 4,048 posts
Joined: Aug 2000
"SELECT TOP 5 * FROM table_name"
Mark Hensler posted this at 04:30 — 13th February 2001.
He has: 4,048 posts
Joined: Aug 2000
forgot... there is an ASP specific thing...
If your trying to pagenate something (like search results), you may want to try playing with the RecordSet object. Check out these links for more info:
http://support.microsoft.com/support/kb/articles/Q202/1/25.ASP
http://msdn.microsoft.com/library/partbook/asp20/apagedresultsetexample.htm
http://www.asp101.com/articles/recordsetpaging/index.asp
http://www.asp101.com/samples/db_paging.asp
Good Luck,
Mark Hensler
If there is no answer on Google, then there is no question.
Gil posted this at 05:21 — 13th February 2001.
They have: 103 posts
Joined: Apr 1999
Maybe I should have been a little more specific.
I have a table that has multiple records with the same information (there is no unique identifier). So when I go to update one of those records, I only want to update one of them. So that's why I wanted to use LIMIT
But Update table TOP 1 SET var='$foo' where var='$oldfoo' doesn't work. It gives me a syntax error. I'm not using ASP for the project, I'm using PHP. Any other suggestions?
Gil Hildebrand, Jr.
Internet Consultant
New Orleans, LA
Mark Hensler posted this at 08:14 — 13th February 2001.
He has: 4,048 posts
Joined: Aug 2000
argh... how many records?
I'm thinking you could create a unique field....
maybe, create a new table with all the correct fields, and an extra (ID) which is unique.
then use a DB data dump (like from phpMyAdmin) and put the data in to the new table.
delete the old table, rename the new to the old.
you could write a PHP script to do this... farly easy..
Why did you ever create a table with no unique field?
Mark Hensler
If there is no answer on Google, then there is no question.
Gil posted this at 13:42 — 13th February 2001.
They have: 103 posts
Joined: Apr 1999
There are a few reasons why I didn't create a UID, but that's not the point. There is no way in SQL Server to do something like LIMIT?
Peter J. Boettcher posted this at 14:11 — 13th February 2001.
They have: 812 posts
Joined: Feb 2000
Gil,
Even when you think you're not going to need it you should always setup some sort of auto integer field to help differentiate records. It adds little to the table's overall size but can be a real blessing down the road. Try the following SQL syntax:
UPDATE TableName
SET FieldName = 'TheValue'
FROM (SELECT TOP 1 * FROM TableName ORDER BY FieldName) AS
foo
WHERE TableName.FieldName = foo.FieldName
Mark,
Are you sure you're not the database moderator?
, maybe I should be visiting the scripting forum more often 
PJ | Are we there yet?
pjboettcher.com
Mark Hensler posted this at 23:21 — 13th February 2001.
He has: 4,048 posts
Joined: Aug 2000
lol, sorry Peter.... stop by the scripting forum anytime
Peter J. Boettcher posted this at 14:09 — 14th February 2001.
They have: 812 posts
Joined: Feb 2000
Mark,
Don't apologize, it's handy to have someone familiar with PHP help me out
I'll browse the scripting forum more often to see if I can help you out with ASP/XML/COM...
PJ | Are we there yet?
pjboettcher.com
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.