CODEWITHSUNDEEP
sqloracle-databaseoracle10g
Abhi
Abhi

Reputation: 55

How to retrieve latest instance using IF else condition in sqldeveloper

I have one student table which is given below

ROLL  |  STUDENT | SUBJECT | VERSION_ID |

  1        A         M         1
  2        B         M         2 
  3        C         M         3
  4        B         S         1
  5        D         S         2
  6        E         E         1 
  7        F         G         1

If there is atleast one record for SUBJECT='M' then retrieve latest record for SUBJECT 'M' based on version id.
ELSIF If there is atleast one record for SUBJECT='S' then retrieve latest record for SUBJECT 'S' based on version id.
ELSIF If there is atleast one record for SUBJECT='E' then retrieve latest record for SUBJECT 'E' based on version id.
Else DO NOT fetch any record.

Based on above condition I am trying to write a query or view which can satisfy all above condition and and give below output. Please suggest me possible solutions to get this.

Output 

ROLL  |  STUDENT | SUBJECT | VERSION_ID | 
  3        C         M         3

I have tried to get desired output using below query but I encountered with error. Please help me to solve this query with better performance. 

SELECT * from ( IF EXISTS(select subject from STUDENT where SUBJECT='M') then
select * from STUDENT S1
inner join
(select S2.ROLL from STUDENT S2
      where 
          S2.ROLL=(select S3.ROLL from STUDENT S3
                       where S3.SUBJECT='M'
                   and S3.VERSION_ID=(select MAX(S4.VERSION_ID) from STUDENT S4
                           GROUP BY S4.SUBJECT
                           HAVING S4.SUBJECT='M'))) S5
ON
S1.ROLL=S5.ROLL
ELSIF EXISTS(select subject from STUDENT where SUBJECT='S') then
select * from STUDENT S1
inner join
(select S2.ROLL from STUDENT S2
      where 
          S2.ROLL=(select S3.ROLL from STUDENT S3
                       where S3.SUBJECT='S'
                   and S3.VERSION_ID=(select MAX(S4.VERSION_ID) from STUDENT S4
                           GROUP BY S4.SUBJECT
                           HAVING S4.SUBJECT='S'))) S5
           ON
          S1.ROLL=S5.ROLL
ELSIF EXISTS(select subject from STUDENT where SUBJECT='E') then
select * from STUDENT S1
inner join
(select S2.ROLL from STUDENT S2
      where 
          S2.ROLL=(select S3.ROLL from STUDENT S3
                       where S3.SUBJECT='E'
                   and S3.VERSION_ID=(select MAX(S4.VERSION_ID) from STUDENT S4
                           GROUP BY S4.SUBJECT
                           HAVING S4.SUBJECT='E'))) S5
           ON
          S1.ROLL=S5.ROLL
ELSE 
  SELECT * FROM STUDENT WHERE 1=2);

TABLE CREATION: 

Create table student (ROLL number,STUDENT varchar2(20),SUBJECT varchar2(20),VERSION_ID number );

Insert Data: 

insert into STUDENT values(1,'A','M',1);
insert into STUDENT values(2,'B','M',2);
insert into STUDENT values(3,'C','M',3);
insert into STUDENT values(4,'B','S',1);
insert into STUDENT values(5,'D','S',2);
insert into STUDENT values(6,'E','E',1);
insert into STUDENT values(7,'F','G',1);

Thank You..

Upvotes: 2

Views: 60

Answers (2)

DeadCat
DeadCat

Reputation: 192

You want right answer right? Try this :

SELECT TOP 1 #student.*
FROM   student
JOIN   (

SELECT  MAX(VERSION_ID) VERSION_ID,SUBJECT
FROM   student
WHERE  SUBJECT = 'M'
GROUP BY SUBJECT

UNION ALL 

SELECT  MAX(VERSION_ID),SUBJECT
FROM   student
WHERE  SUBJECT = 'S'
GROUP BY SUBJECT

UNION ALL

SELECT  MAX(VERSION_ID),SUBJECT
FROM   student
WHERE  SUBJECT = 'E'
GROUP BY SUBJECT

UNION ALL 

SELECT  MAX(VERSION_ID),SUBJECT
FROM   student
WHERE  SUBJECT = 'G'
GROUP BY SUBJECT

UNION ALL

SELECT NULL,NULL

)       AS Dtls
ON     (student.SUBJECT     =      Dtls.SUBJECT
AND student.VERSION_ID     =      Dtls.VERSION_ID)

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522331

We can apply ROW_NUMBER to entire table, using the ordering logic you gave in your question. Subjects M, S, and E have priority, in that order, with the VERSION_ID breaking the tie. Rows with a subject not matching these three are assigned the lowest priority. Should there be no records with matching subjects, we filter out all records in the WHERE clause.

WITH cte AS (
    SELECT ROLL, STUDENT, SUBJECT, VERSION_ID,
        ROW_NUMBER() OVER (ORDER BY CASE WHEN SUBJECT = 'M' THEN 1
                                         WHEN SUBJECT = 'S' THEN 2
                                         WHEN SUBJECT = 'E' THEN 3
                                         ELSE 4 END, VERSION_ID DESC END) rn
)

SELECT ROLL, STUDENT, SUBJECT, VERSION_ID
FROM cte
WHERE rn = 1 AND SUBJECT IN ('M', 'S', 'E')

Upvotes: 3

Related Questions