CODEWITHSUNDEEP
sqloracle-database
gymcode
gymcode

Reputation: 4623

Oracle Joining 2 Select Statements With Only One Record From Each Query

Currently I have 2 queries that needs to be run. I am trying to retrieve the first record of each query and combining them together.

Summary of the question: Get top scorers of both category, senior and over60, and display the results.

Below is the query that I am running:

SELECT * FROM
(SELECT C2.*, RE.POINTS
FROM COMPETITION C1, COMPETITOR C2, RESULTS RE, REGISTRATION REG
WHERE REG.CATEGORYTYPE IN ('SENIOR')
AND C1.TIMEPLANNED BETWEEN TO_DATE('01-JAN-15') AND TO_DATE ('31-DEC-15')
AND REG.COMPETITIONID = RE.COMPETITONID
AND RE.COMPETITORID = C2.COMPETITORID
AND RE.COMPETITONID = C1.COMPETITIONID
ORDER BY RE.POINTS DESC)
WHERE ROWNUM=1;

The second query is very similar to the first, only making changes in the categorytype

WHERE REG.CATEGORYTYPE IN ('OVER60')

May I know how can I go about joining these 2 statements together?

Thank you.

Upvotes: 0

Views: 32

Answers (2)

dnoeth
dnoeth

Reputation: 60482

Switch to ROW_NUMBER:

SELECT * FROM
 ( 
   SELECT C2.*, RE.POINTS,
      ROW_NUMBER()
      OVER (PARTITION BY REG.CATEGORYTYPE
            ORDER BY RE.POINTS DESC) AS rn
   FROM COMPETITION C1, COMPETITOR C2, RESULTS RE, REGISTRATION REG
   WHERE REG.CATEGORYTYPE IN ('SENIOR', 'OVER60')
     AND C1.TIMEPLANNED BETWEEN TO_DATE('01-JAN-15') AND TO_DATE ('31-DEC-15')
     AND REG.COMPETITIONID = RE.COMPETITONID
     AND RE.COMPETITORID = C2.COMPETITORID
     AND RE.COMPETITONID = C1.COMPETITIONID
 )
WHERE rn=1;

Upvotes: 1

Mohanrao Kolisetti
Mohanrao Kolisetti

Reputation: 29

Try this : Add this in your where clause REG.CATEGORYTYPE in('senior','over60')

Upvotes: 0

Related Questions