Efficiently find top-N values from multiple columns independently in Oracle
Suppose I have 30 billion rows with multiple columns, and I want to efficiently find the top N most-frequent values for each column independently, and with the most elegant SQL possible. For example, if I have 

    FirstName LastName FavoriteAnimal FavoriteBook
    --------- -------- -------------- ------------
    Ferris    Freemont Possum         Ubik
    Nancy     Freemont Lemur          Housekeeping
    Nancy     Drew     Penguin        Ubik
    Bill      Ribbits  Lemur          Dhalgren

and I want the top-1, then the result would be:

    FirstName LastName FavoriteAnimal FavoriteBook
    --------- -------- -------------- ------------
    Nancy     Freemont Lemur          Ubik

I can probably think of ways to do it, but not sure if they're optimal, which is important when there are 30 billion rows; and the SQL might be big and ugly, and maybe too much temp space would be used.

Using Oracle.


Efficiently find top-N values from multiple columns independently in Oracle
===

Suppose I have 30 billion rows with multiple columns, and I want to efficiently find the top N most-frequent values for each column independently, and with the most elegant SQL possible. For example, if I have 

    FirstName LastName FavoriteAnimal FavoriteBook
    --------- -------- -------------- ------------
    Ferris    Freemont Possum         Ubik
    Nancy     Freemont Lemur          Housekeeping
    Nancy     Drew     Penguin        Ubik
    Bill      Ribbits  Lemur          Dhalgren

and I want the top-1, then the result would be:

    FirstName LastName FavoriteAnimal FavoriteBook
    --------- -------- -------------- ------------
    Nancy     Freemont Lemur          Ubik

I can probably think of ways to do it, but not sure if they're optimal, which is important when there are 30 billion rows; and the SQL might be big and ugly, and maybe too much temp space would be used.

Using Oracle.

