4
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?

MT0
  • 143,790
  • 11
  • 59
  • 117
Rudziankoŭ
  • 10,681
  • 20
  • 92
  • 192
  • Possible duplicate of [Using the MIN function in the having clause](https://stackoverflow.com/questions/19761714/using-the-min-function-in-the-having-clause) – NikNik Aug 09 '17 at 08:35

2 Answers2

5

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;
MT0
  • 143,790
  • 11
  • 59
  • 117
2

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
JohnHC
  • 10,935
  • 1
  • 24
  • 40
  • Using `ROW_NUMBER` will only give the same answer as the OP's query if the `assessmentId` column is `UNIQUE`. If multiple rows can have the same minimum `assessmentId` then this will not give the same answer. – MT0 Aug 09 '17 at 09:04