Clever Geek Handbook

How can I "Sort by min (subquery)"?

I am trying to return a list of dates. Each line has multiple dates concatenated into one line. I want to order the rows by minimum date in each row.

Here is my request:

  SELECT br.bm_tracking_number,
         (SELECT TOLIST(APPT.fact_date) 
            FROM bm_fact APPT 
           WHERE APPT.bm_review_sk = br.bm_review_sk
             AND APPT.fact_type_code=183050) "Appointments"
    FROM BM_REVIEW br
   WHERE row_delete_date_time IS NULL
ORDER BY MIN(SELECT APPT.fact_date 
               FROM bm_fact APPT
              WHERE APPT.bm_review_sk = br.bm_review_sk
               AND APPT.fact_type_code = 183050);

I am getting Oracle error (missing expression 00936) in turn. Is there any other way to accomplish what I am trying to do?

+2


source to share


2 answers


It:

  SELECT br.bm_tracking_number,
         TOLIST(bf.fact_date)
    FROM BM_REVIEW br
    JOIN BM_FACT bf ON bf.bm_review_sk = br.bm_review_sk
                   AND bf.fact_type_code = 183050
   WHERE br.row_delete_date_time IS NULL
GROUP BY br.bm_tracking_number
ORDER BY MIN(bf.fact_date)


... will give you a list ordered fact_date

in ascending order.

+3


source


Just by looking at it, but one thing is that you are not an alias bm_fact for APPT in a subquery.


EDIT:



Ok, you fixed that.

How about putting MIN () inside an auxiliary query?

ORDER BY (SELECT MIN(APPT.fact_date) ...)
0


source






More articles:


All Articles