Query formation

order by nvl2(rordr_shrt_rqst_end_ts,2,1), FNSH_STK_CD desc,FSC_DESC,ACTN_DESC desc

or you could try...


order by rordr_shrt_rqst_end_ts desc nulls first, FNSH_STK_CD desc nulls first,FSC_DESC,ACTN_DESC desc nulls first

Thanks for your response.

I elaborated in detail below.

By default  rordr_shrt_rqst_end_ts nulls should be first..

I will pass other columns like FNSH_STK_CD,FSC_DESC,ACTN_DESC  in the query and the first result set (By default  rordr_shrt_rqst_end_ts nulls should be first..) should be joined with this result set.

Example :

CASE 1: TS NULLS FIRST

FSC  ACTION   TS
110  SHORT    EMPTY
210  REORDER  EMPTY
310  SHORT    NOT EMPTY
410  SHORT    NOT EMPTY

CASE 2: TS NULLS FIRST AND THEN FSC DESC

FSC  ACTION   TS
210  SHORT    EMPTY
110  REORDER  EMPTY
410  SHORT    NOT EMPTY
310  SHORT    NOT EMPTY

CASE 3: TS NULLS FIRST AND THEN ACTION DESC

FSC  ACTION     TS
310  SHORT      EMPTY
210  REORDER    EMPTY
110  SHORT      NOT EMPTY
210  REORDER    NOT EMPTY

AND FOR REMAINING COLUMNS ALSO I HAVE TO WORK OUT SOMETHING LIKE THIS.

Please help me resolve.

Regards.

Try:

(select case rordr_shrt_rqst_end_ts when Null then 0 else 1 end as TS from sir_fsc_cncltn_rqst) desc

In your Order by to sort on Null vs Non-Null for rordr_shrt_rqst_end_ts.

This will return more than one row.
(select case rordr_shrt_rqst_end_ts when Null then 0 else 1 end as TS from sir_fsc_cncltn_rqst) desc

how to use it in my query , can yuo form and show me,please?

I'm a little confused about what you want to accomplish. Can you provide some sample data with your expected results and a little more clarification on why it should be those results?

Can you please check the image i posted.

And also the above post where I explained by seperate cases.

Basically two sets of result set, in first set always ordered by rordr_shrt_rqst_end_ts is not null.

second set is based on the other columns and both result should be joined horizontally.

That is how it should be displayed.

Please help me resolve this.

Okay I will try to explain it properly.

SEE attached image.. named 1st and 2nd.

1st is the result set when rordr_shrt_rqst_end_ts is null ..

2nd is the result set when rordr_shrt_rqst_end_ts is not null..

I need the first result set to always be coming at the top of the entire result set and this result set can also be ordered by other columns of the same result set but it should always come at the begining of the entire result set.

The second result set needs to be ordered by all the possible columns and it should be horizaontally joined to the first result set.

Please check the image..1st and 2nd, please help me now.
1st.JPG
2nd.JPG

Framed it myself..don't know how i got stuck on this..

/* Formatted on 2012/03/21 23:35 (Formatter Plus v4.8.8) */
SELECT *
  FROM ((SELECT *
           FROM (SELECT   fnsh_stk_cd, fsc_desc, actn_desc,
                          rordr_shrt_rqst_end_ts
                     FROM sir_fsc_cncltn_rqst
                    WHERE 1 = 1
                      AND work_dt = TO_DATE ('22/03/2012', 'dd/MM/yyyy')
                      AND in68_cd = 121
                      AND (fnsh_stk_cd, rordr_shrt_rqst_strt_ts) IN (
                             SELECT   fnsh_stk_cd,
                                      MAX (rordr_shrt_rqst_strt_ts)
                                 FROM sir_fsc_cncltn_rqst
                                WHERE work_dt =
                                          TO_DATE ('22/03/2012', 'dd/MM/yyyy')
                                  AND in68_cd = 121
                             GROUP BY fnsh_stk_cd)
                      AND rordr_shrt_rqst_end_ts IS NULL
                 ORDER BY rordr_shrt_rqst_end_ts NULLS FIRST, fsc_desc DESC) h)
        UNION ALL
        (SELECT *
           FROM (SELECT   fnsh_stk_cd, fsc_desc, actn_desc,
                          rordr_shrt_rqst_end_ts
                     FROM sir_fsc_cncltn_rqst
                    WHERE 1 = 1
                      AND work_dt = TO_DATE ('22/03/2012', 'dd/MM/yyyy')
                      AND in68_cd = 121
                      AND (fnsh_stk_cd, rordr_shrt_rqst_strt_ts) IN (
                             SELECT   fnsh_stk_cd,
                                      MAX (rordr_shrt_rqst_strt_ts)
                                 FROM sir_fsc_cncltn_rqst
                                WHERE work_dt =
                                          TO_DATE ('22/03/2012', 'dd/MM/yyyy')
                                  AND in68_cd = 121
                             GROUP BY fnsh_stk_cd)
                      AND rordr_shrt_rqst_end_ts IS NOT NULL
                 ORDER BY rordr_shrt_rqst_end_ts DESC) b)) c

Thanks for all your responses..

Regards..

If you can help me shorten above query. please help me.

you appear to have changed the question.


do you need anything more on the original sorting?
if not, please close this question and open a new one with your new requirements.

I've requested that this question be closed as follows:

Accepted answer: 0 points for neoarwin's comment #37748913

for the following reason:

The answer is correct.

I solved it myself.

you are using the NULLS FIRST/LAST method shown in the first post.

however,  you are using a UNION which is completely unnecessary and inefficient

based on your final post,  the first posts would do what you asked
if it does not, please explain how it differs

between the original question and your last version of the query you made a small change to the columns you want to use in the sort, but the methods shown above still apply.    Combining your last version with the "nulls first" shown above would look like this...


SELECT   fnsh_stk_cd, fsc_desc, actn_desc, rordr_shrt_rqst_end_ts
    FROM sir_fsc_cncltn_rqst
   WHERE 1 = 1
     AND work_dt = TO_DATE ('22/03/2012', 'dd/MM/yyyy')
     AND in68_cd = 121
     AND (fnsh_stk_cd, rordr_shrt_rqst_strt_ts) IN (
            SELECT   fnsh_stk_cd, MAX (rordr_shrt_rqst_strt_ts)
                FROM sir_fsc_cncltn_rqst
               WHERE work_dt = TO_DATE ('22/03/2012', 'dd/MM/yyyy')
                 AND in68_cd = 121
            GROUP BY fnsh_stk_cd)
order by rordr_shrt_rqst_end_ts desc nulls first, fsc_desc DESC