CODEWITHSUNDEEP
sqloracle-databaseplsql
Ahmad
Ahmad

Reputation: 109

SQL Trouble Understanding query

I have the following query 

SELECT * FROM TABLEA C
WHERE C.VAL = 'SENIOR' 
AND C.TYPE = 'HELLO'
AND C.DATE = (SELECT MAX(DATE) from TABLEA WHERE TYPE = 
'HELLO'  AND C.ID=ID)

I am having trouble understanding what the C.ID=ID in the inner query actually does in this query. I am not sure if it is possible to understand the query and answer for anyone without demo data but I do not have a demo data at the moment.

Upvotes: 1

Views: 44

Answers (1)

D.L.
D.L.

Reputation: 324

Basically, it ensures that rows in the subquery only match rows in the the outer query with the same ID. However, if TABLEA.ID is a primary key, then the subquery looks a bit redundant. Probably best to illustrate with an example. Let's assume ID is not a primary key so there can be multiple rows with the same ID:

ID        VAL       TYPE      DATE
1         SENIOR    HELLO     01-JAN-1970
1         SENIOR    HELLO     02-JAN-1970
1         JUNIOR    HELLO     03-JAN-1970
1         SENIOR    HELLO     03-JAN-1970
2         SENIOR    HELLO     04-JAN-1970
2         JUNIOR    HELLO     05-JAN-1970
2         SENIOR    HELLO     05-JAN-1970
2         JUNIOR    BYE       06-JAN-1970
3         JUNIOR    BYE       06-JAN-1970

The subquery ensures that only the row with max(date) and type HELLO will be selected from rows with the same id so the result with the above data would return:

ID        VAL       TYPE      DATE
1         JUNIOR    HELLO     03-JAN-1970
2         JUNIOR    HELLO     05-JAN-1970

Basically, it would just return the row with the newest date for rows with a given id that are also of type HELLO and val SENIOR so long as the row with the newest date also has a val of SENIOR.

Upvotes: 1

Related Questions