Trying to figure out top 5 land areas of the 50 states in the U.S

Question

I have a table created. With one column named states and another column called land area. I am using oracle 11g. I have looked at various questions on here and cannot find a solution. Here is what I have tried so far:

SELECT LandAreas, State 
FROM ( SELECT LandAreas, State, DENSE_RANK() OVER (ORDER BY State DESC) sal_dense_rank
       FROM Map ) 
WHERE sal_dense_rank >= 5;

This does not provide the top 5 land areas as far as number wise.

I have also tried this one but no go either:

SELECT * FROM Map order by State desc) 
where rownum < 5;

Anyone have any suggestions to get me on the right track??

Here is a samle of the table

states      land areas
michagan    15000
florida     25000
tennessee   10000
alabama     80000
new york    150000
california  20000
oregon      5000
texas       6000
utah        3000
nebraska    1000

Desired output from query:

States      land area
new york    150000
alabama     80000
florida     25000
california  20000

Answer 1

Try:

Select * from 
(SELECT State, LandAreas FROM Map ORDER BY LandAreas DESC)
where rownum < 6

Link to Fiddle

Answer 2

Try something like this

select m.states,m.landarea from map m where (select count(‘x’) from map m2 where m2.landarea > m.landarea)<=5 order by m.landarea

Answer 3

Use a HAVING clause and count the number state states larger:

SELECT m.state, m.landArea
FROM Map m
LEFT JOIN Map m2 on m2.landArea > m.landArea
GROUP BY m.state, m.landArea
HAVING count(*) < 5
ORDER BY m.landArea DESC

See SQLFiddle

This joins each state to every state whose area is greater, then uses a HAVING clause to return only those states where the number of larger states was less than 5.

Ties are all returned, leading to more than 5 rows in the case of a tie for 5th.

The left join is needed for the case of the largest state, which has no other larger state to join to.

The ORDER BY is optional.

Answer 4

There are two bloomers in your posted code.

1. You need to use landarea in the DENSE_RANK() call. At the moment you're ordering the states in reverse alphabetical order.
2. Your filter in the outer query is the wrong way around: you're excluding the top four results.

Here is what you need ...

SELECT LandArea, State 
FROM ( SELECT LandArea
              , State
              , DENSE_RANK() OVER (ORDER BY landarea DESC) as area_dr
       FROM Maps ) 
WHERE area_dr <= 5
order by area_dr;

... and here is the SQL Fiddle to prove it. (I'm going with the statement in the question that you want the top 5 biggest states and ignoring the fact that your desired result set has only four rows. But adjust the outer filter as you will).

---

There are three different functions for deriving top-N result sets: DENSE_RANK, RANK and ROW_NUMBER.

Using ROW_NUMBER will always guarantee you 5 rows in the result set, but you may get the wrong result if there are several states with the same land area (unlikely in this case, but other data sets will produce such clashes). So: 1,2,3,4,5

The difference between RANK and DENSE_RANK is how they handle ties. DENSE_RANK always produces a series of consecutive numbers, regardless of how many rows there are in each rank. So: 1,2,2,3,3,3,4,5

RANK on the other hand will produce a sparse series if a given rank has more than one hit. So: 1,2,2,4,4,4.

Note that each of the example result sets has a different number of rows. Which one is correct? It depends on the precise question you want to ask.

Using a sorted sub-query with the ROWNUM pseudo-column will work like the ROW_NUMBER function, but I prefer using ROW_NUMBER because it is more powerful and more error-proof.