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.