CODEWITHSUNDEEP
sqloracle-database
veronik
veronik

Reputation: 177

How to select a table based on a value in a analytical function Oracle SQL

I have a list of dates and I want to find out which one occurs the earliest in the year, I used a dense rank function to only extract the date and the month, but I can't get it to return all the values equal to 1 (there may be multiple earliest dates not just one).

SELECT
    S.SG_HOSTCITY,
    C.COUNTRY_OLYMPIC_CODE,
    DENSE_RANK() OVER (ORDER BY to_char(S.SG_START, 'MMDD')) AS RN
FROM
    SUMMERGAMES S,
    COUNTRY C  
WHERE
    S.COUNTRY_ISOCODE = C.COUNTRY_ISOCODE
    RN = 1
ORDER BY RN;

Just spits out 00933. 00000 - "SQL command not properly ended"

Can anyone help? I don't know what I'm doing wrong.

Upvotes: 0

Views: 55

Answers (1)

Brian DeMilia
Brian DeMilia

Reputation: 13248

Put it into an inline view:

select SG_HOSTCITY, COUNTRY_OLYMPIC_CODE
  from (SELECT S.SG_HOSTCITY,
               C.COUNTRY_OLYMPIC_CODE,
               DENSE_RANK() OVER(ORDER BY to_char(S.SG_START, 'MMDD')) AS RN
          FROM SUMMERGAMES S
          join COUNTRY C
            on S.COUNTRY_ISOCODE = C.COUNTRY_ISOCODE)
 WHERE RN = 1

You can't use the WHERE clause to filter in on the output values of an analytic function within the same query. You have to put it into a subquery. The above is the same as your current query but is free of syntax errors.

However I don't know if it will actually give you the output you're expecting. I might also try:

select *
  from (SELECT S.SG_HOSTCITY,
               C.COUNTRY_OLYMPIC_CODE,
               DENSE_RANK() OVER( partition by TRUNC(S.SG_START, 'YYYY')
                                  order BY TRUNC(S.SG_START)              ) AS RN
          FROM SUMMERGAMES S
          join COUNTRY C
            on S.COUNTRY_ISOCODE = C.COUNTRY_ISOCODE)
 WHERE RN = 1

This will give you combinations of SG_HOSTCITY and COUNTRY_OLYMPIC_CODE falling on the first SG_START date associated with each year. If the first of the year 2002 is 1/5, for instance, and there are 5 such SG_HOSTCITY and COUNTRY_OLYMPIC_CODE values falling on that date for year 2002, this will show all 5 for that year, because it will bring back ties.

The difference is that the rank ascends and then restarts at the change in each year, not throughout all years (notice the partition).

I'm thinking the second query above is what you really want.

Upvotes: 2

Related Questions