Find the latest or earliest date

Question

I have a table with a foreign key called team_ID, a date column called game_date, and a single char column called result. I need to find when the next volleyball game happens. I have successfully narrowed the game dates down to all the volleyball games that have not happened yet because the result IS NULL. I have all the select in line, I just need to find the earliest date.

Here is what I've got:

SELECT game.game_date, team.team_name
FROM game
JOIN team
ON team.team_id = game.team_id
WHERE team.sport_id IN
    (SELECT sport.sport_id
    FROM sport
    WHERE UPPER(sport.sport_type_code) IN
        (SELECT UPPER(sport_type.sport_type_code)
        FROM sport_type
        WHERE UPPER(sport_type_name) like UPPER('%VOLLEYBALL%')
        )
    )
AND game.result IS NULL;

I'm a time traveler so don't mind the old dates.

When I run it, I get this:

GAME_DATE   TEAM_NAME
----------- ----------
11-NOV-1998 BEars
13-NOV-1998 BEars
13-NOV-1998 WildCats
14-NOV-1998 BEars

How do I set it up so I get only the MIN(DATE) and the TEAM_NAME playing on that date?

I've tried AND game.game_date = MIN(game.game_date) but it simply tells me that a group function in not allowed here. There has to be a way to retrieve the MIN(game_date) and use it as a condition to be met.

I'm using Oracle 11g pl/sql.

This should be the final working code.

SELECT *
  FROM 
    (
    SELECT g.game_date, t.team_name
      FROM game g
      JOIN team t
        ON t.team_id = g.team_id
      JOIN sport s
        ON t.sport_id = s.sport_id
      JOIN sport_type st
        ON UPPER(s.sport_type_code) IN UPPER(st.sport_type_code)
     WHERE UPPER(sport_type_name) like UPPER('%VOLLEYBALL%')
       AND g.result IS NULL
  ORDER BY g.game_date
    )
WHERE ROWNUM = 1;

That "final" code is not guaranteed to work as the `ROWNUM` psuedocolumn is generated before the `ORDER BY` clause is applied so you will get the first row in whatever order the database produces the rows and discard the rest and then will order the data. It appears to be working as the database is coincidentally producing the minimum row first but it is not guaranteed to work (and in most cases it won't work) — MT0, Mar 18 '16 at 23:36

MT0 · Accepted Answer · 2016-03-18T23:40:41.700

3

The ROWNUM pseudocolumn is generated before any ORDER BY clause is applied to the query. If you just do WHERE ROWNUM <= X then you will get X rows in whatever order Oracle produces the data from the datafiles and not the X minimum rows. To guarantee getting the minimum row you need to use ORDER BY first and then filter on ROWNUM like this:

SELECT *
FROM   (
  SELECT g.game_date, t.team_name
  FROM   game g
         JOIN team t
         ON t.team_id = g.team_id
         INNER JOIN sport s
         ON t.sport_id = s.sport_id
         INNER JOIN sport_type y
         ON UPPER( s.sport_type_code ) = UPPER( y.sport_type_code )
  WHERE  UPPER( y.sport_type_name) LIKE UPPER('%VOLLEYBALL%')
  AND    g.result IS NULL
  ORDER BY game_date ASC -- You need to do the ORDER BY in an inner query
)
WHERE ROWNUM = 1; -- Then filter on ROWNUM in an outer query.

If you want to return multiple rows with the minimum date then:

SELECT game_date,
       team_name
FROM   (
  SELECT g.game_date,
         t.team_name,
         RANK() OVER ( ORDER BY g.game_date ASC ) AS rnk
  FROM   game g
         JOIN team t
         ON t.team_id = g.team_id
         INNER JOIN sport s
         ON t.sport_id = s.sport_id
         INNER JOIN sport_type y
         ON UPPER( s.sport_type_code ) = UPPER( y.sport_type_code )
  WHERE  UPPER( y.sport_type_name) LIKE UPPER('%VOLLEYBALL%')
  AND    g.result IS NULL
)
WHERE rnk = 1;

edited Mar 18 '16 at 23:40

answered Mar 18 '16 at 23:23

MT0

143,790
11
59
117

Can you explain the purpose of `rank()`? Also, I'm not finding any online resources that talk about the `over` function you used. – Blu Mar 19 '16 at 00:03
[`RANK`](https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions123.htm) will rank the rows (similar to applying `ROWNUM`) but if multiple rows have equal value (based on the `ORDER BY` clause) then they will have the identical ranks (whereas `ROWNUM` will give each row a the next number in the sequence regardless of whether they have equal value or not). You can think or rank as if 2 runners have exactly the same time in a race then they will share the same podium place. – MT0 Mar 19 '16 at 00:10
Ok, after some struggle I finally got a working code using what you described. – Blu Mar 19 '16 at 00:47
I do want to know why `rank` doesn't have any arguments in its parenthesis. – Blu Mar 19 '16 at 00:52
`RANK() OVER ()` is using the analytic syntax and not the aggregate syntax. See the link in my previous comment. – MT0 Mar 19 '16 at 00:55
Oh. I get it. It's just bizarre to me to have that kind of syntax. – Blu Mar 19 '16 at 01:06

score 0 · Answer 2 · answered Mar 18 '16 at 23:04

0

Could you make it simple and order by date and SELECT TOP 1? I think this is the syntax in Oracle:

WHERE ROWNUM <= number;

answered Mar 18 '16 at 23:04

Abraham Lincoln

263
3
16

That solves the problem at hand. I'll mark you as the answer once the site lets me but I'm probably going to make another question that specifically asks for a way to use a value retrieved from a `MIN` or `MAX` function as a condition to be met in a `WHERE COLUMN = MIN(COLUMN)`. – Blu Mar 18 '16 at 23:19
1

It is not quite as simple as adding `ROWNUM <= X` to the `WHERE` clause as the `ROWNUM` pseudocolumn is generated before any `ORDER BY` clause is applied to the query. If you just do `WHERE ROWNUM <= X` then you will get `X` rows in whatever order Oracle produces the data from the datafiles and not the `X` minimum rows (and in the OP's example it just happens that the first row being produced is the minimum so this would appear to work when it is not guaranteed to do so). – MT0 Mar 18 '16 at 23:27
In that case, you might want to read this: http://stackoverflow.com/questions/11978136/how-can-i-get-just-the-first-row-in-a-result-set-after-ordering – Abraham Lincoln Mar 18 '16 at 23:41

score 0 · Answer 3 · answered Mar 19 '16 at 12:17

select game.game_date,team.team_name from (
SELECT game.game_date, team.team_name, rank() over (partition by team.team_name order by game.game_date asc) T
FROM game
JOIN team
ON team.team_id = game.team_id
WHERE team.sport_id IN
    (SELECT sport.sport_id
    FROM sport
    WHERE UPPER(sport.sport_type_code) IN
        (SELECT UPPER(sport_type.sport_type_code)
        FROM sport_type
        WHERE UPPER(sport_type_name) like UPPER('%VOLLEYBALL%')
        )
    )
AND game.result IS NULL 
) query1 where query1.T=1;

Find the latest or earliest date

3 Answers3