To find the value of lowest DensityPerKmSq, query is below.
Tuesday, 24 January 2012
Wednesday, 18 January 2012
Max Function. To find the value of highest DensityPerKmSq.
To find the value of highest DensityPerKmSq, query is below.
If you want to find the country with highest DensityPerKmSq, please refer the post on "Max Function.To find the country with highest DensityPerKmSq"
Tuesday, 3 January 2012
To find the Nth densest Country.
Consider for example, To find the 6th densest country, query is below.
Row Number,Rank and Dense Rank Functions
We can now see about 'Row_Number' , 'Rank' and 'Dense_Rank' Functions.Query is below.
Hope the query can be understood with the below explaination.I have partitioned the output by continent.Say for example,within the continent of America, the Row Number (,Rank and Dense Rank) are distributed among the countries of USA and Brazil. Similarly, the Row Number (, Rank and Dense Rank) are once again starting from the beginning for the other countries of Asia, Australia and Europe.We can see that by default the countries are ordered by ascending order by the column 'country'.
The difference between 'Row_Number' , 'Rank' and 'Dense_Rank' can be understood by our same countrytable example by removing the partition by clause. Query is below.
Please note that here the rows are ordered by continent and not by country. Now the difference between 'Row_Number' , 'Rank' and 'Dense Rank' are clearly visible. There are two rows with continent name 'America'. Even though there is a tie(between both the rows having America as continent), both have different Row Numbers but same Ranks and Dense Ranks. Since there are two countries with same Continent 'America' ,the Rank 2 is skipped and since there are five Asian countries, Ranks 4, 5, 6, 7 are skipped(There are four European countries. Hence ranks 10, 11, 12 are skipped). Dense Rank as the name suggests, there is no gap between the Ranks.
It is also possible that if there is a tie with same continent,then the 'Ranks' (, 'Row_Numbers' and 'Dense_Ranks') can consider the other columns for numbering.Consider the below query.
Hope the query can be understood with the below explaination.I have partitioned the output by continent.Say for example,within the continent of America, the Row Number (,Rank and Dense Rank) are distributed among the countries of USA and Brazil. Similarly, the Row Number (, Rank and Dense Rank) are once again starting from the beginning for the other countries of Asia, Australia and Europe.We can see that by default the countries are ordered by ascending order by the column 'country'.
The difference between 'Row_Number' , 'Rank' and 'Dense_Rank' can be understood by our same countrytable example by removing the partition by clause. Query is below.
Please note that here the rows are ordered by continent and not by country. Now the difference between 'Row_Number' , 'Rank' and 'Dense Rank' are clearly visible. There are two rows with continent name 'America'. Even though there is a tie(between both the rows having America as continent), both have different Row Numbers but same Ranks and Dense Ranks. Since there are two countries with same Continent 'America' ,the Rank 2 is skipped and since there are five Asian countries, Ranks 4, 5, 6, 7 are skipped(There are four European countries. Hence ranks 10, 11, 12 are skipped). Dense Rank as the name suggests, there is no gap between the Ranks.
It is also possible that if there is a tie with same continent,then the 'Ranks' (, 'Row_Numbers' and 'Dense_Ranks') can consider the other columns for numbering.Consider the below query.
For Rank Column, We could see the columns are ordered by continent in ascending order(America, Asia, Australia, Europe). If there is a tie, say two rows with continent 'America', then rank is given by countrywise in desending order(USA and then Brazil).Similarly Asian contries are also ordered as Sri Lanka, Pakistan, Nepal, India, China(Descending order). European countries are also in descending order(UK, Italy, Germany, France).
Subscribe to:
Posts (Atom)