CODEWITHSUNDEEP
sqloracle-database
Rudziankoŭ
Rudziankoŭ

Reputation: 11251

Oracle: select values only from row with min(id)

SELECT
   ass.assessmentAmount -- want to fetch assessmentAmount of min(ass.assessmentId)
   ass.assessmentId
FROM
   --bunch of joins  
WHERE
ass.assessmentId = (SELECT min(ass2.assessmentId) FROM Assessment ass2
   --same bunch of joins

It looks very confusing because I have 6 joins with conditions and I don't want to repeat it two times. Is there another way of doing this?

Upvotes: 5

Views: 11874

Answers (2)

MT0
MT0

Reputation: 167867

Use the MIN( ass.assessmentId ) OVER () analytic function:

SELECT *
FROM   (
  SELECT ass.assessmentAmount,
         ass.assessmentId,
         MIN( ass.assessmentId ) OVER () AS min_assessmentId
  FROM   --bunch of joins
)
WHERE assessmentId = min_assessmentId;

You can also use RANK():

SELECT *
FROM   (
  SELECT ass.assessmentAmount,
         ass.assessmentId,
         RANK() OVER ( ORDER BY ass.assessmentId ) AS rnk
  FROM   --bunch of joins
)
WHERE rnk = 1;

If assessmentId is UNIQUE and can only have a single row as a minimum then you could replace RANK with ROW_NUMBER; however, you could also then get the desired result using the ROWNUM pseudocolumn:

SELECT *
FROM   (
  SELECT ass.assessmentAmount,
         ass.assessmentId
  FROM   --bunch of joins
  ORDER BY ass.assessmentId ASC
)
WHERE ROWNUM = 1;

Upvotes: 6

JohnHC
JohnHC

Reputation: 11195

Use a CTE with a row_number

with CTE as
(
select assessmentId, 
       assessmentAmount , 
       row_number() over (order by assessmentid asc) as rn
from --bunch of joins
)
select *
from CTE
where rn = 1

Upvotes: 2

Related Questions