| Group By - Top records [message #219342] |
Wed, 14 February 2007 01:33  |
helloanam
Messages: 20
Registered: August 2005
|
Junior Member |
|
|
i have table with customer address information. the problem is that, have to select top 100 from each postcode.
i know that i can group the records with postcodes using
select customer_id, postcode
from customer_table
group by postcode,customer_id
now the prob is that how should i select the top 100 from each postcode. Am i going in right direction???
any help would be appreciated.
|
|
|
|
|
|
|
|
| Re: Group By - Top records [message #219428 is a reply to message #219342] |
Wed, 14 February 2007 06:05  |
helloanam
Messages: 20
Registered: August 2005
|
Junior Member |
|
|
hi all
i think i have created some confusion.
what the problem is......
lets us suppose we have customers who are in 10 different postcodes. now we have to display top 100 customers from each postcode. so for
postcode 1 - top 100 records
postcode 2 - top 100 records
.
.
.
.
.
postcode 10 - top 100 records
actually in real problem i am unaware of the number of postcodes in the address table. therefore its dynamic to display all the top 100 records of each postcode. the problem is that we can't use cursors for this.
only single query is required.
|
|
|
|
| Re: Group By - Top records [message #219442 is a reply to message #219428] |
Wed, 14 February 2007 07:39 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
This is reasonably straightforwards to do, once you can tell us 'How do we identify which customers are to be in the top 100'?
If there is no criteria to order the customers by, all we can do is to effectively get you 100 random customers for each postcode.
|
|
|
|
| Re: Group By - Top records [message #219523 is a reply to message #219428] |
Wed, 14 February 2007 13:57 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
i do not see any problem with this using analytics:
SELECT *
FROM ( SELECT postcode, customer_id, <any other columns,>
ROW_NUMBER() OVER (PARTITION BY postcode ORDER BY <anything you deduce top record>) rnum
FROM customer_table )
WHERE rnum <= 100
ORDER BY postcode <, rnum or any other columns you want>;
you really should get familiar with analytic functions, they help a lot.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
