Results 1 to 13 of 13
  1. #1
    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?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    SELECT TOP 1000 field1, field2 ...

  3. #3
    Join Date
    Dec 2003
    Posts
    74
    TRY THIS
    SELECT * FROM TABLENAME WHERE ROWNUM <=1000
    Last edited by edwinjames; 12-18-03 at 12:36.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Originally posted by edwinjames
    TRY THIS
    SELECT * FROM TABLENAME WHERE ROWNUM =10
    I think you meant:

    SELECT * FROM TABLENAME WHERE ROWNUM <= 1000;

  5. #5
    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?

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    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

  7. #7
    Join Date
    Nov 2003
    Posts
    23
    It doesn't appear to. I can't get ROWNUM or TOP to work.

  8. #8
    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.

  9. #9
    Join Date
    Nov 2003
    Posts
    23
    Ahhhh, sorry. I'm using Sybase ASE.

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    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 11:38.

  11. #11
    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

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    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"

  13. #13
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •