0

A median is defined as a number separating the higher half of a data set from the lower half. Query the median of the Northern Latitudes (LAT_N) from STATION and round your answer to decimal places.

Input Format

The STATION table is described as follows:

enter image description here

where LAT_N is the northern latitude and LONG_W is the western longitude.

I wrote the query as:

select round(LAT_N,4) from (select LAT_N from station order by LAT_N) where ROWNUM = ((LENGTH(LAT_N)+1)/2);

I am not getting the output from this query. Can anyone explain what's the error?

Aayush Chachan
  • 35
  • 5
  • 2
    "Wrong Answer" is highly unlikely to be a message from an SQL compiler. The LENGTH() function is something quite different from the cardinality of a relation, which must be obtained using SELECT COUNT(*) FROM ... Whether your solution is dependable, depending on ROWNUM being always equal to a row's rank, may be uncertain. – Erwin Smout Jul 17 '18 at 13:28
  • Which database server do you use ? Please use appropriate tag. – Dragan Jovanović Jul 17 '18 at 13:29
  • 1
    I would love to know what compiler it is that returns such a intriguing error message as "Wrong Answer" :) – Joakim Danielson Jul 17 '18 at 13:31
  • @DraganJovanović Oracle. I was practicing on online website. – Aayush Chachan Jul 17 '18 at 13:38
  • @JoakimDanielson Sorry my bad. Compiler is not throwing Wrong Answer as error. As I was practicing online so it was just checking with the correct output. – Aayush Chachan Jul 17 '18 at 13:40
  • https://stackoverflow.com/a/2026609/1693085 – John Bustos Jul 17 '18 at 13:48

2 Answers2

3

In Oracle, probably the simplest way is to use median():

select median(LAT_N) 
from stations;
Gordon Linoff
  • 1,122,135
  • 50
  • 484
  • 624
  • Thanks.. But can you explain what's wrong in my query. – Aayush Chachan Jul 17 '18 at 14:03
  • 1
    @AayushChachan `rownum` is assigned to rows as they are returned, so the first row is 1, the next is 2 etc. Therefore predicates like `where rownum = 2` will never be true. Also, `length(lat_n)` returns the number of characters in the string (e.g. `length('xyz')` returns 3), not the number of rows in a set. – William Robertson Jul 17 '18 at 21:42
0

Try it this way :

select top 1 a.LAT_N, count(*) biggerThan
from
(select LAT_N from station) a join (select LAT_N from station) b on a.LAT_N > b.LAT_N
    join (select count(*) countt from station) counterr on 1 = 1
group by a.LAT_N, counterr.countt
having count(*) > counterr.countt/2 - 1
order by biggerThan

It returns the smallest value which is bigger than approximately half of set members. I have defined "approximately half" as countt/2 - 1, and you should probably be more precise with this part of logic.

Dragan Jovanović
  • 120
  • 6