Imagine after running a query like this:

SELECT ContactName, Country FROM Customers ORDER BY Country

on Northwind database and getting following result:

ContactName           Country
Patricio Simpson      Argentina
Yvonne Moncada        Argentina
Sergio Gutiérrez      Argentina
Georg Pipps           Austria
Roland Mendel         Austria
Catherine Dewey       Belgium
Pascale Cartrain      Belgium
Anabela Domingues     Brazil
Paula Parente         Brazil
Bernardo Batista      Brazil
Lúcia Carvalho        Brazil
Janete Limeira        Brazil
Aria Cruz             Brazil
André Fonseca         Brazil
Mario Pontes          Brazil
Pedro Afonso          Brazil
Elizabeth Lincoln     Canada
Jean Fresnière        Canada
Yoshi Tannamuri       Canada
...

You’re asked to retreive only the 2nd and the 5th group of contacts. How do you do it? DENSE_RANK() is the answer. Modify the query like this:

SELECT ContactName, Country, DENSE_RANK() OVER (ORDER BY Country) FROM Customers

The DENSE_RANK() function returns rank (number) of row group where partition is specified in OVER(ORDER BY…) clause. Here is the result:

ContactName           Country            No column name
Patricio Simpson      Argentina          1
Yvonne Moncada        Argentina          1
Sergio Gutiérrez      Argentina          1
Georg Pipps           Austria            2
Roland Mendel         Austria            2
Catherine Dewey       Belgium            3
Pascale Cartrain      Belgium            3
Anabela Domingues     Brazil             4
Paula Parente         Brazil             4
Bernardo Batista      Brazil             4
Lúcia Carvalho        Brazil             4
Janete Limeira        Brazil             4
Aria Cruz	          Brazil             4
André Fonseca         Brazil             4
Mario Pontes          Brazil             4
Pedro Afonso          Brazil             4
Elizabeth Lincoln     Canada             5
Jean Fresnière        Canada             5
Yoshi Tannamuri       Canada             5
...

Now the only thing left is to select group with rank 2 and 5:

SELECT ContactName, Country FROM
 (SELECT ContactName, Country, DENSE_RANK() OVER (ORDER BY Country) AS GROUP_NO
  FROM Customers) AS Cust
WHERE GROUP_NO IN (2,5)

The result:

ContactName           Country
Georg Pipps           Austria
Roland Mendel         Austria
Elizabeth Lincoln     Canada 
Jean Fresnière        Canada
Yoshi Tannamuri       Canada