How can i put IF ELSE CONDITION in Oracle query?

Question

I have a table, A and B which are shown below,

Table A:

id
idB
name
faculty

B:

id
name

Table B has 2 records as below.

SELECT *
FROM B;

 1, 1, 'First'
 2, 2, 'Second'

Table A has 8 records as below.

SELECT *
FROM A;

 1, 1, A, IT
 2, 1, B, IT
 3, 1, C, IT
 4, 1, D, Medicine
 5, 1, E, Medicine
 6, 1, F, Business
 7, 1, G, Business
 8, 1, H, IT

 9, 2, A, Medicine
 10, 2, B, Medicine
 11, 2, C, Medicine
 12, 2, D, Medicine
 13, 2, E, Medicine
 14, 2, F, Medicine
 15, 2, G, Business
 16, 2, H, Medicine

My question is:

How can I select data from table B where faculty should be IT and if there are multiple it should get with max ID. AND if there is no any IT, it should be get business?

My select view should be look like this below:

A and B records.

8, 1, H, IT, First
15, 2, G, Business, Second

Please could you advise and help in which way we can retrieve these data?

`where faculty in ('IT', 'Business')`? – Apr 24 '18 at 10:18 — , Apr 24 '18 at 10:18
I have edited my question, sorry for misunderstanding – Nureke Apr 24 '18 at 10:55 — Nureke, Apr 24 '18 at 10:55

Jay Shankar Gupta · Answer 1 · 2018-04-25T11:47:29.993

1

SELECT A.*,T.Name
FROM 
TableA A
INNER JOIN
(
SELECT MAX(A.id) AS id,A.FACULTY,B.NAME,
  ROW_NUMBER() OVER (PARTITION BY B.NAME ORDER BY MAX(A.id) DESC) AS RN FROM TableA A
INNER JOIN TableB B
ON A.idB=B.id
WHERE faculty IN ('IT', 'Business')
GROUP BY A.FACULTY,B.NAME
) T
ON A.id = T.id
AND A.FACULTY = T.FACULTY
WHERE T.RN=1

Output

ID  IDB  NAME   FACULTY   NAME
8   1    H      IT        First
15  2    G      Business  Second

Demo

http://sqlfiddle.com/#!4/98b84/24

edited Apr 25 '18 at 11:47

answered Apr 24 '18 at 10:23

Jay Shankar Gupta

5,918
1
10
27

I have edited my question, sorry for misunderstanding – Nureke Apr 24 '18 at 10:55
Check Now I Edited my answer @Nureke – Jay Shankar Gupta Apr 24 '18 at 11:39
Could I check it if faculty = it, then max id, idb and so on, And if faculty=it does not exist, then, look faculty = business; could i do something like that – Nureke Apr 25 '18 at 11:46

score 1 · Answer 2 · answered Apr 24 '18 at 10:24

1

this query will help you to get your desired result

SELECT id
    ,name
    ,faculty
FROM A
WHERE faculty IN ('IT', 'Business')

answered Apr 24 '18 at 10:24

Istiaque Hossain

2,157
1
17
28

score 1 · Answer 3 · answered Apr 24 '18 at 11:33

How can I select data from table B where faculty should be IT and if there are multiple it should get with max ID. AND if there is no any IT, it should be get business?

This will get the row with the maximum ID that is in IT and if there are no IT rows then Business

SELECT *
FROM   (
  SELECT A.id,
         A.idB,
         A.name,
         A.faculty,
         B.name AS bname
  FROM   A
         INNER JOIN B
         ON ( A.idB = B.id )
  WHERE  A.faculty IN ( 'IT', 'Business' )
  ORDER BY
         DECODE( A.faculty, 'IT', 1, 'Business', 2 ),
         A.id DESC
)
WHERE  ROWNUM = 1;

If you want the top row from each group then:

SELECT id,
       idB,
       name,
       faculty,
       bname
FROM   (
  SELECT A.id,
         A.idB,
         A.name,
         A.faculty,
         B.name AS bname,
         ROW_NUMBER() OVER (
           PARTITION BY A.faculty
           ORDER BY     A.id DESC
         ) AS rn
  FROM   A
         INNER JOIN B
         ON ( A.idB = B.id )
  WHERE  A.faculty IN ( 'IT', 'Business' )
)
WHERE  rn = 1;

How can i put IF ELSE CONDITION in Oracle query?

3 Answers3

Linked

Related