LIMIT Statement - Dev Shed

Dev Shed Forums Sponsor:

Get inside! Sample the range of functionality easily built with JMSL Library for Time Series Data Analysis, Heat Maps, Portfolio Optimization, Monte Carlo Simulation, Stock Price Charting and more. Download Now!

January 9th, 2004, 06:47 AM

z-d-m

Junior Member

Join Date: Jan 2004

Posts: 1

Time spent in forums: < 1 sec
Reputation Power: 0

LIMIT Statement

Greetings from germany,

I need to limit my results. I worked with a mySQL database.
There the syntax was like:

Select table.* From table
LIMIT 0,3

That means I want to recieve 3 results max.

But it doesnt work with the ms sql Server!

Can somebody help me please.

Thank you,
z-d-m

January 9th, 2004, 08:03 AM

r937

SQL Consultant

Dev Shed God 25th Plane (17000 - 17499 posts)

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,344

Time spent in forums: 1 Month 3 Weeks 5 Days 8 h 27 m 14 sec
Reputation Power: 891

select top 3 * from yourtable

this really only makes sense (and in the mysql version as well) if you have an ORDER BY clause

__________________
r937.com | rudy.ca

February 23rd, 2004, 12:02 PM

rksubbu

Contributing User

Join Date: Apr 2003

Location: Jacksonville, FL

Posts: 91

Time spent in forums: 1 h 36 m 59 sec
Reputation Power: 6

how about between two numbers

How about between rows 5 and 15? In MySQL, I could do like

Select * from table limit 5,15;

I did not find a similar implementation in MSSql. Does it not exist?

Thanks,
rks

Quote:

Originally Posted by r937

select top 3 * from yourtable

this really only makes sense (and in the mysql version as well) if you have an ORDER BY clause

February 23rd, 2004, 01:24 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,344

Time spent in forums: 1 Month 3 Weeks 5 Days 8 h 27 m 14 sec
Reputation Power: 891

yeah, it exists, but it's ugly

Code:

select *
  from (
       select top 10 foo, bar
         from (
              select top 15 foo, bar
                from thetable
              order
                  by bar desc
              ) as dt1
       order 
           by bar asc
       ) as dt2           
order 
    by bar desc

February 23rd, 2004, 08:46 PM

rksubbu

Contributing User

Join Date: Apr 2003

Location: Jacksonville, FL

Posts: 91

Time spent in forums: 1 h 36 m 59 sec
Reputation Power: 6

Oh ! vow!! I am surprised.. How does MySql has such flexibility and MS SQL does not! Anyway, I know now how to do it. Thank you very much.

Quote:

Originally Posted by r937

yeah, it exists, but it's ugly

Code:

select *
  from (
       select top 10 foo, bar
         from (
              select top 15 foo, bar
                from thetable
              order
                  by bar desc
              ) as dt1
       order 
           by bar asc
       ) as dt2           
order 
    by bar desc

February 24th, 2004, 03:24 AM

pabloj

Modding: Oracle MsSQL Firebird

Dev Shed God 6th Plane (7500 - 7999 posts)

Join Date: Jun 2001

Location: Outside US

Posts: 7,711

Time spent in forums: 2 Months 2 Weeks 6 Days 5 h 22 m 58 sec
Reputation Power: 259

I would suggest you to take a look at this article about paging records with stored procedures.

__________________
My blog about OpenSource Databases
PDF tutorials about OSS databases, DBMonster ...
Please contribute to Open Source Development, fill bug reports!!!
Developer Shed eSupport
Commented my.ini/my.cnf (PLEASE ADD YOUR OWN CONFIG TRICK)
An introduction to database normalization
Natural or Surrogate key
Custom ordering for your results
Correlated and uncorrelated subqueries
Don't turn your outer joins into inner joins

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: