| Author |
Topic |
|
dogdaynoon
Starting Member
5 Posts |
 Posted - 2011-07-06 : 17:18:30
|
| Using SQL Express on 64 bit Windows Server 2008 R2I have this statement: "SELECT * FROM table_name WHERE column_name LIKE "%$variable%" OR column_name LIKE "%$variable%" OR column_name LIKE "%$variable%" ORDER BY column_name LIMIT $variable, $variable;"But I am getting an error "Incorrect syntax near 'LIMIT'. Any idea what the incorrect syntax is??? Any help would be appreciated.Thanks,dogdaynoon |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-06 : 17:21:12
|
| That's MySQL syntax (LIMIT), not SQL Server syntax.What are you trying to do?--Gail ShawSQL Server MVP |
 |
|
|
dogdaynoon
Starting Member
5 Posts |
Posted - 2011-07-06 : 17:26:59
|
| LOL that's so funny. I am trying to switch MySQL statements over to SQL. I also tried the same as above only with LIMIT $variable OFFSET $variable and got the same error.I am trying to return a list but only show x amount of results per page.It was working as MySQL but i am just starting out with this SQL stuff. I am using the sqlsrv drivers for php->SQL.Thank you for the quick response! |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-07 : 05:27:16
|
| LIMIT is a MySQL keyword. It does not work in MS SQL Server at all, no matter what way around you write it.Paging's a much harder thing to do in SQL Server. Depending on version there may be some decent options. So what version of SQL Server--Gail ShawSQL Server MVP |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-07-07 : 05:34:28
|
| Put the code in a stored procedure and pass parameters.Not sure how your variables work but from your first post maybe something like"with cte as (SELECT *, seq = row_number() over (order by column_name) FROM table_name WHERE column_name LIKE "%$variable%" OR column_name LIKE "%$variable%" OR column_name LIKE "%$variable%") select * from cte where seq between $variable and $variable ORDER BY column_name"==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
dogdaynoon
Starting Member
5 Posts |
Posted - 2011-07-07 : 14:08:15
|
| @Gila Monster: SQL Express 2008 64 bit@Nigelrivet: I will look into your solution. I wil need to learn more about the following... cte, seq = row_number(), over(). Then maybe your answer will make a little more sense to me. Thanks for the replies. I won't be able to look at this again till next week. But i will certainly do some reading before my next post. dogdaynoon |
|
|
|
dogdaynoon
Starting Member
5 Posts |
Posted - 2011-07-13 : 17:22:44
|
| "with cte as (SELECT *, seq = row_number() over (order by column_name) FROM table_name WHERE column_name LIKE "%$variable%" OR column_name LIKE "%$variable%" OR column_name LIKE "%$variable%") select * from cte where seq between $variable and $variable ORDER BY column_name"Well that seemed to work, but could you please explain to me what is happening at this point in the code SELECT *, seq = row_number()Thanks a bunch.dogdaynoon |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-13 : 17:50:11
|
| * means all columns.The row_number is a built in function that assigns numbers to rows based on the ordering and partitioning you set.--Gail ShawSQL Server MVP |
|
|
|
dogdaynoon
Starting Member
5 Posts |
Posted - 2011-07-13 : 17:51:44
|
| Yeah i get the select * part... what is the "seq =" doing? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-13 : 18:00:06
|
Aliasing the column. It's the older, less preferred style. with cte as ( SELECT *, row_number() over (order by column_name) AS seq FROM table_name WHERE column_name LIKE "%$variable%" OR column_name LIKE "%$variable%" OR column_name LIKE "%$variable%") select * from cte where seq between $variable and $variable ORDER BY column_name --Gail ShawSQL Server MVP |
|
|
|
|
Help with a LIMIT error??? [SOLVED]