SQL: Return the first / top n rows of each group

For example, you have a product table containing the following columns.

Name                Type         
----------            ------------
Apple                Fruit
Banana              Fruit
Walnut               Nut
Papaya               Fruit
Cashew              Nut
Pine Nut             Nut

You want your SQL to return the top 2 rows from each Type, a result like this.

Type                   Name
---------              ------------------------------
Fruit                  Apple
Fruit                  Banana
Nut                    Cashew
Nut                    Pine Nut

The SQLs below shall help.

SQL> select Type, Name
           from (
                           select Type, Name, 
                             ROW_NUMBER() OVER (PARTITION BY Type ORDER BY Type, Name) as type_rank
                           from product

                    ) AS product 

            where type_rank <= 2;

OR

SQL> WITH prequery AS (
                  select Type,
                             Name,
                             ROW_NUMBER() OVER (PARTITION BY Type ORDER BY Type, Name) as type_rank
                  from product
              )
              SELECT Type, Name,
              FROM prequery
              WHERE type_rank <= 2;

----------------------------------------------------------------------------------------------------------------------

                        
If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.

SQL: select a unique/distinct subset of columns and also show the other columns - partial distinct / unique columns

For example, you have this FRUIT table in your database. (This is just an example, it contains repeating data that need to be normalized :-)).

Name          Price         Date                   Place
Apple          3.28          1/10/2015          Darloo
Apple          3.28           1/22/2015         Jumbo
Orange        2.50           1/15/2015         Darloo
Orange        2.50           1/19/2015         Jumbo

Lets say that you want to get unique Name and Price only but the result also show Date and Place. Some thing looks like this.

Apple          3.28          1/10/2015          Darloo
Orange        2.50           1/19/2015         Jumbo

If you use the SQL: SELECT unique Name, Price, Date, Place FROM FRUIT to query the table, you will get all the rows with repeating Name and Price.

You may use the following SQL to get the result you want.

SELECT Name, Price, Date, Place
FROM (
       SELECT FRUIT.*, row_number() over (partition by Name, Price order by Name)  as rn
       FROM FRUIT) FRUIT
WHERE rn = 1;

----------------------------------------------------------------------------------------------------------------------

                        
If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.