Home » SQL & PL/SQL » SQL & PL/SQL » Group By - Top records
Group By - Top records [message #219342] Wed, 14 February 2007 01:33 Go to next message
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 #219348 is a reply to message #219342] Wed, 14 February 2007 02:10 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
top 100? what would be the criteria?
Search
By
Vamsi
Re: Group By - Top records [message #219349 is a reply to message #219342] Wed, 14 February 2007 02:10 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Check OraFAQ page about How does one select the TOP N rows from the table.
Re: Group By - Top records [message #219428 is a reply to message #219342] Wed, 14 February 2007 06:05 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: getting the number of current connections
Next Topic: save as.......
Goto Forum:
  


Current Time: Sat Dec 14 14:40:09 CST 2024