Waldar's SQLing and Datawarehousing Place

WITH students AS
(
SELECT 1 AS stud_id, 'JAMES' AS name, date '1989-07-14' AS birthdate FROM dual union ALL
SELECT 2, 'JOHN'   , date '1991-02-07' FROM dual union ALL
SELECT 3, 'ROBERT' , date '1986-08-23' FROM dual union ALL
SELECT 4, 'MICHAEL', date '1983-12-02' FROM dual union ALL
SELECT 5, 'WILLIAM', date '1988-03-02' FROM dual union ALL
SELECT 6, 'DAVID'  , date '1984-05-12' FROM dual union ALL
SELECT 7, 'RICHARD', date '1984-06-06' FROM dual union ALL
SELECT 8, 'CHARLES', date '1984-09-07' FROM dual
),
course AS
(
SELECT 1 AS cour_id, 1 AS stud_id FROM dual union ALL
SELECT 2, 2 FROM dual union ALL
SELECT 1, 3 FROM dual union ALL
SELECT 3, 4 FROM dual union ALL
SELECT 2, 5 FROM dual union ALL
SELECT 2, 6 FROM dual union ALL
SELECT 1, 7 FROM dual union ALL
SELECT 1, 8 FROM dual
)
SELECT
    co.cour_id,
    max(st.name) keep (dense_rank last  sdfklgjkldfgjkldf BY st.birthdate ASC) AS youngest,
    max(st.name) keep (dense_rank first ORDER BY st.birthdate ASC) AS oldest
FROM
    students st
    INNER JOIN course co
      ON co.stud_id = st.stud_id
GROUP BY
    co.cour_id
ORDER BY
    co.cour_id ASC;

COUR_ID    YOUNGEST    OLDEST
---------- ----------- -----------
1          JAMES       RICHARD
2          JOHN        DAVID
3          MICHAEL     MICHAEL

SELECT
    co.cour_id,
    max(st.name) keep (dense_rank last partition BY co.cour_id) AS test --order by count(*) desc) AS test
FROM
    students st
    INNER JOIN course co
      ON co.stud_id = st.stud_id
GROUP BY
    co.cour_id
ORDER BY
    co.cour_id ASC

SELECT
    co.cour_id,
    max(st.name) keep (dense_rank last ORDER     BY birthdate DESC) AS test1,
    max(st.name) keep (dense_rank last partition BY birthdate DESC) AS test2,
    max(st.name) keep (dense_rank last this      BY birthdate DESC) AS test3,
    max(st.name) keep (dense_rank last IS        BY birthdate DESC) AS test4,
    max(st.name) keep (dense_rank last strange   BY birthdate DESC) AS test5,
    max(st.name) keep (dense_rank last BY        BY birthdate DESC) AS test6,
    max(st.name) keep (dense_rank last BYE       BY birthdate DESC) AS test7
FROM
    students st
    INNER JOIN course co
      ON co.stud_id = st.stud_id
GROUP BY
    co.cour_id
ORDER BY
    co.cour_id ASC

SELECT
    co.cour_id,
    max(st.name) keep (dense_rank last BY birthdate DESC) AS test
FROM
    students st
    INNER JOIN course co
      ON co.stud_id = st.stud_id
GROUP BY
    co.cour_id
ORDER BY
    co.cour_id ASC;

ORA-00924 : missing BY keyword
Cause: The keyword BY was omitted IN a GROUP BY, ORDER BY, OR CONNECT BY clause.
    IN a GRANT statement, the keyword IDENTIFIED must also be followed BY the keyword BY.
Action: Correct the syntax.
    INSERT the keyword BY WHERE required AND then retry the statement.