Solved

LIMIT FOR MSSQL

Posted on 2014-03-25
5
257 Views
Last Modified: 2014-04-02
we have this query for search ..

$query = " SELECT * FROM dbo.Rooms WHERE ROOMNO NOT IN (SELECT DISTINCT S_ROOMNO FROM dbo.Stay WHERE ((S_ADATE BETWEEN '$CheckIn' AND '$CheckOut') AND (S_DDATE BETWEEN '$CheckIn' AND '$CheckOut')) OR (S_ADATE < '$CheckIn' AND S_DDATE > '$CheckOut') OR (S_ADATE < '$CheckIn' AND ( S_DDATE BETWEEN '$CheckIn' AND '$CheckOut')) OR (( S_ADATE BETWEEN '$CheckIn' AND '$CheckOut' ) AND S_DDATE > '$CheckOut')) ";


after fetch result we need to  add this pagination.


$offset = ($currentpage - 1) * $rowsperpage + 1;
$limit=($offset-1) + $rowsperpage;
$query .= "LIMIT $offset , $limit";


but this LIMIT is not working with MSSQL Server , it works for Mysql  but i don't know how to do it in MSSQL to  work with  PHP.

Any help is greatly appreciated!!

Tony
0
Comment
Question by:tonyhhisc
  • 2
5 Comments
 
LVL 4

Accepted Solution

by:
gozoliet earned 500 total points
ID: 39955015
In SQL 2012 you can use fetch/offset
SELECT email FROM emailTable 
WHERE id=3
ORDER BY Id
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

Open in new window


Previous versions would include row_number() over a sort order and then select rows from that table where the row number is between your page values.
$query = " SELECT *, row_number() over ( orDer by roomno) as rownum
 FROM dbo.Rooms WHERE ROOMNO NOT IN (SELECT DISTINCT S_ROOMNO FROM dbo.Stay WHERE ((S_ADATE BETWEEN '$CheckIn' AND '$CheckOut') AND (S_DDATE BETWEEN '$CheckIn' AND '$CheckOut')) OR (S_ADATE < '$CheckIn' AND S_DDATE > '$CheckOut') OR (S_ADATE < '$CheckIn' AND ( S_DDATE BETWEEN '$CheckIn' AND '$CheckOut')) OR (( S_ADATE BETWEEN '$CheckIn' AND '$CheckOut' ) AND S_DDATE > '$CheckOut')) ";

$offset = ($currentpage - 1) * $rowsperpage + 1;
$limit=($offset-1) + $rowsperpage;
$query .= "And rownum between $offset and $limit";

Open in new window

0
 

Author Comment

by:tonyhhisc
ID: 39955992
I have did like this  first  time and no  work  with me  :/ , if  can give all code to  help will add it.

KInd regards,
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39958553
no work  with me
What, exactly, happened?  Did you get any output at all?  Did you get an error message?  If you can give us the SSCCE or at least some clues to go on we might be able to produce better answers.

I believe that MSSQL has a TOP clause.  There is some discussion of this here:
http://forums.asp.net/t/1224860.aspx?SELECT+TOP+MSSQL

There is also OFFSET / FETCH.
http://technet.microsoft.com/en-us/library/gg699618.aspx
0
 

Author Closing Comment

by:tonyhhisc
ID: 39972476
thanks for your help.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now