Solved

LIMIT FOR MSSQL

Posted on 2014-03-25
5
262 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 110

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Viewers will learn how the fundamental information of how to create a table.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

707 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