| |
|
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.
|
 |
|
05-15-08, 14:32
|
|
Registered User
|
|
Join Date: Dec 2005
Posts: 69
|
|
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 14:44.
|
05-15-08, 15:12
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,156
|
|
Have a look for the TOP keyword in MSSQL BOL
|
|
05-15-08, 16:39
|
|
Registered User
|
|
Join Date: Dec 2005
Posts: 69
|
|
|
|
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
|
|
05-15-08, 16:52
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
|
|
"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
|
|
05-15-08, 16:59
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,156
|
|
TOP is meaningless without an ORDER BY clause.
If you want the top 5 order ascending, bottom 5 descending.
|
|
05-16-08, 03:19
|
|
Registered User
|
|
Join Date: Jan 2007
Location: Jena, Germany
Posts: 2,662
|
|
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
|
|
05-17-08, 17:17
|
|
Registered User
|
|
Join Date: Sep 2004
Location: Belgium
Posts: 1,079
|
|
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/
|
|
05-17-08, 17:24
|
|
Registered User
|
|
Join Date: Sep 2004
Location: Belgium
Posts: 1,079
|
|
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/
|
|
05-19-08, 10:44
|
|
Registered User
|
|
Join Date: Dec 2005
Posts: 69
|
|
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
|
|
05-19-08, 10:58
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,156
|
|
CTEs are standard? Cool 
|
|
05-19-08, 11:36
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
|
|
if you sql server guys would only lift your head out of the punch bowl once in a while...
|
|
05-19-08, 11:42
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,156
|
|
Rudy, I've only just moved onto 2005 from 6.5 - I'm a little behind on things 
|
|
05-19-08, 12:10
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
|
|
oh george...
SQL 2005 supports CTEs
|
|
05-19-08, 12:39
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,156
|
|
Yes... that was kind of my point.
Never mind 
|
|
01-30-09, 06:37
|
|
Registered User
|
|
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!"
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
How to return a fixed number of rows?
