how to retrieve latest data from a table

Currently I am working on a project where I need to extract latest data from a table for a report purpose. Below is the sample table structure:-

Every student has several courses and course_id for programming language is +ve and for non programming language -ve. I want to extract latest programming language and non programming language course_id for each and every student.

I use the below SQL query and able to extract the data.

CREATE TABLE COURSE
    ("STUDENT_ID" int, "COURSE_ID" int, "COURSE_NAME" varchar2(31), "COURSE_START_DATE" timestamp)
;

INSERT ALL 
    INTO COURSE ("STUDENT_ID", "COURSE_ID", "COURSE_NAME", "COURSE_START_DATE")
         VALUES (100001, -100, 'C Programming Language', '04-Feb-2019 12:00:00 AM')
    INTO COURSE ("STUDENT_ID", "COURSE_ID", "COURSE_NAME", "COURSE_START_DATE")
         VALUES (100001, -200, 'Java Programming Language', '11-Feb-2019 12:00:00 AM')
    INTO COURSE ("STUDENT_ID", "COURSE_ID", "COURSE_NAME", "COURSE_START_DATE")
         VALUES (100001, -300, 'C# Programming Language', '07-Feb-2019 12:00:00 AM')
    INTO COURSE ("STUDENT_ID", "COURSE_ID", "COURSE_NAME", "COURSE_START_DATE")
         VALUES (100001, 100, 'Data Structure and algorithms', '05-Feb-2019 12:00:00 AM')
    INTO COURSE ("STUDENT_ID", "COURSE_ID", "COURSE_NAME", "COURSE_START_DATE")
         VALUES (100001, 200, 'Computer Graphics', '13-Feb-2019 12:00:00 AM')
    INTO COURSE ("STUDENT_ID", "COURSE_ID", "COURSE_NAME", "COURSE_START_DATE")
         VALUES (100001, 300, 'Networking', '02-Feb-2019 12:00:00 AM')
    INTO COURSE ("STUDENT_ID", "COURSE_ID", "COURSE_NAME", "COURSE_START_DATE")
         VALUES (100002, -300, 'C# Programming Language', '12-Feb-2019 12:00:00 AM')
    INTO COURSE ("STUDENT_ID", "COURSE_ID", "COURSE_NAME", "COURSE_START_DATE")
         VALUES (100002, -400, 'Python Programming Language', '07-Feb-2019 12:00:00 AM')
    INTO COURSE ("STUDENT_ID", "COURSE_ID", "COURSE_NAME", "COURSE_START_DATE")
         VALUES (100002, -500, 'JavaScript Programming Language', '08-Feb-2019 12:00:00 AM')
    INTO COURSE ("STUDENT_ID", "COURSE_ID", "COURSE_NAME", "COURSE_START_DATE")
         VALUES (100002, 100, 'Data Structure and algorithms', '17-Jan-2019 12:00:00 AM')
    INTO COURSE ("STUDENT_ID", "COURSE_ID", "COURSE_NAME", "COURSE_START_DATE")
         VALUES (100002, 300, 'Computer Graphics', '26-Jan-2019 12:00:00 AM')
    INTO COURSE ("STUDENT_ID", "COURSE_ID", "COURSE_NAME", "COURSE_START_DATE")
         VALUES (100002, 400, 'DataBase Management', '10-Jan-2019 12:00:00 AM')
    INTO COURSE ("STUDENT_ID", "COURSE_ID", "COURSE_NAME", "COURSE_START_DATE")
         VALUES (100003, -500, 'JavaScript Programming Language', '07-Feb-2019 12:00:00 AM')
    INTO COURSE ("STUDENT_ID", "COURSE_ID", "COURSE_NAME", "COURSE_START_DATE")
         VALUES (100003, -600, 'SQL', '13-Feb-2019 12:00:00 AM')
    INTO COURSE ("STUDENT_ID", "COURSE_ID", "COURSE_NAME", "COURSE_START_DATE")
         VALUES (100003, -200, 'Java Programming Language', '17-Jan-2019 12:00:00 AM')
    INTO COURSE ("STUDENT_ID", "COURSE_ID", "COURSE_NAME", "COURSE_START_DATE")
         VALUES (100003, 300, 'Networking', '04-Feb-2019 12:00:00 AM')
    INTO COURSE ("STUDENT_ID", "COURSE_ID", "COURSE_NAME", "COURSE_START_DATE")
         VALUES (100003, 400, 'DataBase Management', '05-Jan-2019 12:00:00 AM')
    INTO COURSE ("STUDENT_ID", "COURSE_ID", "COURSE_NAME", "COURSE_START_DATE")
         VALUES (100003, 600, 'Cryptography', '18-Jan-2019 12:00:00 AM')
SELECT * FROM dual
;

SELECT STUDENT_ID
,COURSE_ID
,COURSE_NAME
,COURSE_START_DATE
  FROM (
SELECT 
ROW_NUMBER() OVER(PARTITION BY STUDENT_ID ORDER BY COURSE_START_DATE DESC) AS ROW_NUM
,STUDENT_ID
,COURSE_ID
,COURSE_NAME
,COURSE_START_DATE
  FROM
COURSE
WHERE COURSE_ID  0) TEMP1 WHERE TEMP1.ROW_NUM = 1;

But the problem is the real table is very large. There are almost 85k rows and this query is taking sometime. Is there any other better approach. I am using Oracle 11g R2. Please suggest

This is SQLfiddle link http://sqlfiddle.com/#!4/b3fe1/8

Answer

You can try below – you need to add PARTITION BY STUDENT_ID,case when course_id<0 then 1 else 2 end order by COURSE_START_DATE DESC in over cluase

SELECT STUDENT_ID,COURSE_ID,COURSE_NAME,COURSE_START_DATE
FROM 
(
  SELECT 
  ROW_NUMBER() OVER(PARTITION BY STUDENT_ID,case when course_id<0 then 1 else 2 end order by COURSE_START_DATE DESC) AS ROW_NUM
, STUDENT_ID,COURSE_ID,COURSE_NAME,COURSE_START_DATE FROM COURSE
 )TEMP1 WHERE TEMP1.ROW_NUM = 1;

Advertisement

Answer