Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Join Date
    Dec 2005
    Posts
    74

    Question Unanswered: How to return a fixed number of rows?

    Forgive me if this question is too basic but I'd like to avoid reinventing the wheel. How do I build a query that would return some predetermined number of rows from a table? Let's say I can COUNT the number of rows and want to get 10% of the rows in a query so that the resulting dataset contains only 0.1*COUNT rows? (Ignore the NULL and non-distinct entries - let's assume the table has a primary key, albeit of the two-column variety). And if the rows are sorted, let's say I'd like to get the last 10%, not the top 10%. I happen to be using MS SQL Server but how about an ANSI SQL solution?
    Thanks!
    Kamen
    Last edited by KamenG; 05-15-08 at 15:44.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Have a look for the TOP keyword in MSSQL BOL
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2005
    Posts
    74
    Thanks, George. I was aware of the MSSQL "TOP" clause but I was hoping for an ANSI SQL solution so I can keep my code portable. Also, it isn't very clear to me how the ordering of rows would affect the results returned with a TOP clause, e.g., when you want the bottom rows.
    Kamen

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "keep your code portable" and "optimize for each dbms" are antithetical

    in microsoft sql server, you'd be nuts to use anything other than TOP with PERCENT

    ansi sql offers ROW_NUMBER OVER but doesn't have percentage, you you'd have to calc that yourself with a COUNT subquery
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    TOP is meaningless without an ORDER BY clause.

    If you want the top 5 order ascending, bottom 5 descending.
    George
    Home | Blog

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    It depends on what you want to have. If you just need some sample, TOP without an ORDER BY might make perfect sense.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by KamenG
    ... want to get 10% of the rows in a query so that the resulting dataset contains only 0.1*COUNT rows? And if the rows are sorted, let's say I'd like to get the last 10%, not the top 10%.
    An ANSI SQL way of achieving this is:
    Code:
    WITH n(n) AS ( SELECT COUNT(*) FROM my_table ) ,
         a(id, other_cols, m) AS
         ( SELECT id, other_cols,
                  ROW_NUMBER() OVER (ORDER BY id DESC)
           FROM   my_table
    )
    SELECT id, other_cols
    FROM   a, n
    WHERE m * 10 <= n
    ORDER BY id
    where my_table.id is the primary key column.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  8. #8
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by KamenG
    Thanks, George. I was aware of the MSSQL "TOP" clause but I was hoping for an ANSI SQL solution so I can keep my code portable. Also, it isn't very clear to me how the ordering of rows would affect the results returned with a TOP clause, e.g., when you want the bottom rows.
    Kamen
    If I'm not mistaken, MySQL also has an "OFFSET" clause, so if you know that the table has 1000 rows and you want the 100 last ones, use "OFFSET 901".
    Imho, "OFFSET" and "LIMIT" should have been SQL standards; maybe they will in the next version of the SQL standard ...
    (Now there are essentially four variants, all non-standard, for selecting the first n rows of a result set: "SELECT TOP n", "LIMIT n", "FETCH FIRST n ROWS ONLY", and "WHERE rownum <= n".)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  9. #9
    Join Date
    Dec 2005
    Posts
    74
    Thank you, guys. Wow, Peter, that's a pretty nifty solution, introducing the "WITH" clause. I had not seen that before and it isn't in my book, which covers up to SQL 2003. Is that a SQL 2006 novelty? I'm still trying to find documentation on WITH so I can better figure out how it works.
    Thanks again!
    Kamen

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    CTEs are standard? Cool
    George
    Home | Blog

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you sql server guys would only lift your head out of the punch bowl once in a while...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Rudy, I've only just moved onto 2005 from 6.5 - I'm a little behind on things
    George
    Home | Blog

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh george...

    SQL 2005 supports CTEs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Yes... that was kind of my point.
    Never mind
    George
    Home | Blog

  15. #15
    Join Date
    Mar 2008
    Posts
    89
    I don't know how it looks in SQL Server, but Oracle offers the handy little rownum pseudo value.

    Code:
    Select * from millions_of_rows
    Where rownum <=100;
    Gives you exactly 100 records.
    "My brain is just no good at being a relational Database - my relations suck real bad!"

Posting Permissions

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