If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Data Access, Manipulation & Batch Languages > ANSI SQL > How do you only select X number of rows

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Posts: 23
How do you only select X number of rows

Is there a way to select only 1000 rows from a database?
Reply With Quote
  #2 (permalink)  
Old
Purveyor of Discontent
 
Join Date: Mar 2003
Location: The Bottom of The Barrel
Posts: 6,102
SELECT TOP 1000 field1, field2 ...
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Dec 2003
Posts: 74
TRY THIS
SELECT * FROM TABLENAME WHERE ROWNUM <=1000

Last edited by edwinjames; 12-18-03 at 11:36.
Reply With Quote
  #4 (permalink)  
Old
Moderator.
 
Join Date: Sep 2002
Location: UK
Posts: 5,171
Quote:
Originally posted by edwinjames
TRY THIS
SELECT * FROM TABLENAME WHERE ROWNUM =10
I think you meant:

SELECT * FROM TABLENAME WHERE ROWNUM <= 1000;
__________________
Tony Andrews
http://tinyurl.com/tonyandrews
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Posts: 23
I get this error:

Msg 207, Level 16, State 4:
Server 'SYB_SERV, Line 1:
Invalid column name 'ROWNUM'.

Am I missing something?
Reply With Quote
  #6 (permalink)  
Old
Purveyor of Discontent
 
Join Date: Mar 2003
Location: The Bottom of The Barrel
Posts: 6,102
Quote:
Originally posted by bgame
I get this error:

Msg 207, Level 16, State 4:
Server 'SYB_SERV, Line 1:
Invalid column name 'ROWNUM'.

Am I missing something?
Does your server not support TOP?

That's the easiest way to do it...

as I said:

SELECT TOP 1000 * FROM yourtable
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Posts: 23
It doesn't appear to. I can't get ROWNUM or TOP to work.
Reply With Quote
  #8 (permalink)  
Old
Moderator.
 
Join Date: Sep 2002
Location: UK
Posts: 5,171
What DBMS are you using? ROWNUM only works on Oracle, and TOP works on SQL Server at least but not on Oracle. Other DBMSs may have other solutions.
__________________
Tony Andrews
http://tinyurl.com/tonyandrews
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Posts: 23
Ahhhh, sorry. I'm using Sybase ASE.
Reply With Quote
  #10 (permalink)  
Old
Purveyor of Discontent
 
Join Date: Mar 2003
Location: The Bottom of The Barrel
Posts: 6,102
Quote:
Originally posted by andrewst
What DBMS are you using? ROWNUM only works on Oracle, and TOP works on SQL Server at least but not on Oracle. Other DBMSs may have other solutions.
Indeed..

I guess the question is, What db are you using? It's probably a simple syntax-specific thing.



edit - Geez you're fast! lol.

Anywho, I think the reserved word for sybase is rowcount..

set rowcount 1000
select *
set rowcount 0

I should also mention that you're going to want to reset rowcount as well.. it will play havock with the rest of your queries if you dont

?

Last edited by Teddy; 12-18-03 at 10:38.
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Posts: 23
thanks teddy and everyone else. i just figured it out right before i read your last message.

again, thanks very much
Reply With Quote
  #12 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
one of the problems this particular forum seems to incur -- all the time --is that people post generic SQL questions here, and then get PL/SQL (Oracle) answers

or they post PL/SQL questions and get generic SQL answers

it's hard to fault them, either, since the name of the forum is SQL and PL/SQL

is that confusing, or what??!!

note that the description of this forum is
Quote:
The Standard Query Language for virtually all relational database systems currently in use. Although most vendors slightly enhance their versions to take advantage of features specific to their own software.
i'm going to recommend to the moderator(s) of this forum that they change this forum to Standard SQL and put a sticky thread on it to direct people with PL/SQL questions to the Oracle forum

rudy
http://r937.com/

p.s. and perhaps another sticky that says "please don't post database-specific solutions, use standard SQL only"
Reply With Quote
  #13 (permalink)  
Old
Purveyor of Discontent
 
Join Date: Mar 2003
Location: The Bottom of The Barrel
Posts: 6,102
Quote:
Originally posted by r937
one of the problems this particular forum seems to incur -- all the time --is that people post generic SQL questions here, and then get PL/SQL (Oracle) answers

or they post PL/SQL questions and get generic SQL answers

it's hard to fault them, either, since the name of the forum is SQL and PL/SQL

is that confusing, or what??!!

note that the description of this forum is

i'm going to recommend to the moderator(s) of this forum that they change this forum to Standard SQL and put a sticky thread on it to direct people with PL/SQL questions to the Oracle forum

rudy
http://r937.com/

p.s. and perhaps another sticky that says "please don't post database-specific solutions, use standard SQL only"
I don't think that's absolutely necessary... however I would make a forum rule that you MUST post which specific db you use with each post. It's only common courtesy and of course in the best interest of the poster, assuming they want a prompt and correct answer.

Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On