SQL select script_Update timestamp

I have written a SQL query where I am getting all the required fields but the update timestamp in not in order

Though I have given as group by and order by in the script. Could someone tell what I need to change in the script below

 SELECT b.cust_id,
    b.cust_nm,
    a.first_nm,
    a.last_nm,
    a.middle_nm,
    a.updt_ts,
    SUM(DECODE(a.status_cd,'pending',1,0))  AS Pending,
    SUM(DECODE(a.status_cd,'Error',1,0))    AS Error,
    SUM(DECODE(a.status_cd,'success',1,0))  AS success,
    SUM(DECODE(a.status_cd,'existing',1,0)) AS Existing,
    SUM(DECODE(a.status_cd,'canceled',1,0)) AS Cancelled,
    SUM(DECODE(a.status_cd,'',1,0))         AS Null_Values
    FROM table a,
    table b
    WHERE A.UPDT_TS BETWEEN TO_DATE('28-JUN-2017 00:00:00','DD-MON-YYYY HH24:MI:SS') AND sysdate
    AND b.cust_id=a.cust_id
    GROUP BY b.cust_id,
    b.cust_nm,
    a.first_nm,
    a.last_nm,
    a.middle_nm,
    a.updt_ts
    ORDER BY b.cust_id,
    b.cust_nm,
    a.updt_ts DESC;
venkatesh SarivisettySenior Software EngineerAsked:
Who is Participating?
 
Nitin SontakkeConnect With a Mentor DeveloperCommented:
I really don't know how to say this....

It appears you are not reading carefully. I already told you, you cannot have it BOTH ways. Please read my comments carefully.

Please put you data in Excel and use Data -> Sort function. You first try to convince yourself logically how the data should look. Then probably you will realise that your expectations are unrealistic.
0
 
Nitin SontakkeDeveloperCommented:
You should consider adding Oracle in topics.

May be a sample output of few rows would help.

This might be a language problem considering English isn't our first language. Do you mean to say that the values in column [updt_ts] are not in correct order when order by clause is applied? - I think so.

What is the datatype of the column [updt_ts]?

Worst case you can convert it to the string and they order by as in:

ORDER BY b.cust_id,
    b.cust_nm,
    to_char(a.updt_ts, 'YYYYMMDDHH24MISS') DESC; -- Or whatever is appropriate syntax.

Open in new window


Disclaimer: I am not quite an Oracle expert.
0
 
venkatesh SarivisettySenior Software EngineerAuthor Commented:
Hi Sir,

Sorry if my requirement created any confusion.

My requirement is to get an output in an order , here is sample output, When I run my script I am getting the output however the rows are not in an order based on the timestamp. I could see 10- Mar-18 Entry on somewhere at 20th row but actually it should come on top I think

12345678      ABC COMPANY      VENKATESH      CONTRERAS            07-MAR-18 09.43.32.678507000 PM      
12345678      ABC COMPANY      RAVI      HERNANDEZ                    06-MAR-18 06.58.42.098288000 PM

Also I have written you syntax but I am getting same output. I have added oracle in my topics too.

Thanks
Venkatesh.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Nitin SontakkeDeveloperCommented:
Okay. Got it.

What is the data type of the column, you didn't mention. I hope it is datetime equivalent in Oracle.

I hope you are looking at the result set correctly. Problem is the rows given by you above in perfect order as expected. You should demonstrate the actual problem output.

I obviously have no idea about your Oracle expertise, so I would just like to mention that asc, desc must be mentioned for EACH column separately. The last desc applies ONLY TO the column just before it and NOT TO the entire ORDER BY column list. Furthermore any absence of asc desc means it is asc for that column. Hope you are aware of this.

Please demonstrate incorrect output.
0
 
venkatesh SarivisettySenior Software EngineerAuthor Commented:
UPDT_TS         NOT NULL TIMESTAMP(6)

I want to get the timestamp should be in a order..
0
 
Nitin SontakkeDeveloperCommented:
Thank you for the datatype looks correct to me (with my very limited knowledge of Oracle, of course!)

I understood that, but you should show an incorrect output. This is very very important.
0
 
venkatesh SarivisettySenior Software EngineerAuthor Commented:
Timestamp
1 07-MAR-18 09.43.32.678507000 PM
2 06-MAR-18 06.58.42.098288000 PM
3 06-MAR-18 06.57.36.608902000 PM
4 06-MAR-18 06.57.19.687368000 PM
5 06-MAR-18 06.53.54.608311000 PM
6 06-MAR-18 02.08.59.169071000 PM
7 06-FEB-18 02.51.13.483417000 AM
8 04-FEB-18 07.00.47.259269000 PM

9 08-MAR-18 10.19.19.970171000 AM - This one should should come before 07-Mar-18
10 08-JAN-18 09.46.38.903834000 AM
11 08-FEB-18 05.59.50.799126000 PM
12 06-MAR-18 08.19.09.216559000 PM - This one should come before 04-Feb-18. That sequence order is missing though we used order by descending.Hope this helps!!
0
 
Nitin SontakkeDeveloperCommented:
First, are you 100% sure that all of these rows that you have shown above belong to a SINGLE cust_id + cust_nm combination? This is very important. I have no way of knowing as you didn't include it in the output.

Second, if you just give a cursory glance, do you have a reason to believe that the dates are actually sorted as strings and not as dates? DO NOT look at it as a date just plain string. Typically meaning is attached by humans, machines don't do that.

My premise is really very plain and simple. More often than not it is a human which makes the mistake and not the machine. It is our understanding of how machine thinks / works is the problem at most of the time.

Finally, did you try the order by clause I had suggested instead?
0
 
venkatesh SarivisettySenior Software EngineerAuthor Commented:
12345678      ABCBANK                                                            07-MAR-18 09.43.32.678507000 PM      0      0      1      0      0
12345678      ABCBANK                                                            06-MAR-18 06.58.42.098288000 PM      1      0      0      0      0
12345678      ABCBANK                                                            06-MAR-18 06.57.36.608902000 PM      1      0      0      0      0
12345678      ABCBANK                                                            06-MAR-18 06.57.19.687368000 PM      0      0      0      0      0
12345678      ABCBANK                                                            06-MAR-18 06.53.54.608311000 PM      1      0      0      0      0
12345678      ABCBANK                                                            06-MAR-18 02.08.59.169071000 PM      0      0      1      0      0
34567890      XYZ BANK                                                      06-FEB-18 02.51.13.483417000 AM      1      0      0      0      0
34567890      XYZ BANK                                                      04-FEB-18 07.00.47.259269000 PM      0      0      1      0      0
98765432      SBH BANK                                                      08-MAR-18 10.19.19.970171000 AM      0      1      0      0      0
98765432      SBH BANK                                                      08-JAN-18 09.46.38.903834000 AM      0      1      0      0      0

Yes I tried yours but same output as before no change in the order
98765432      SBH BANK                                                      08-FEB-18 05.59.50.799126000 PM      0      1      0      0      0
98765432      SBH BANK                                                      06-MAR-18 08.19.09.216559000 PM      0      0      1      0      0
0
 
Nitin SontakkeDeveloperCommented:
Excellent. Now the output given by you looks perfect to me.

Now, please stay with me here.

Take EXACTLY the same output that you have pasted above and tell me where precisely you think that it is not been ordered correctly.
0
 
venkatesh SarivisettySenior Software EngineerAuthor Commented:
The timestamp column
0
 
Nitin SontakkeDeveloperCommented:
I now notice that it exactly same rows that you had pasted before but WITHOUT cust_id and cust_nm columns.

Please note that the values in column UPDT_TS  will be ordered WITHIN the cust_id and cust_nm combination and NOT for the entire result set. Hope you get that.
0
 
venkatesh SarivisettySenior Software EngineerAuthor Commented:
I now notice that it exactly same rows that you had pasted before but WITHOUT cust_id and cust_nm columns. Yes correct.

Please note that the values in column UPDT_TS  will be ordered WITHIN the cust_id and cust_nm combination and NOT for the entire result set. Hope you get that. -- I didnt understand, cant we get order all the rows based on the timestamp.??
0
 
Nitin SontakkeDeveloperCommented:
You see that the timestamp values are perfectly descending order WITHIN the combination of custoer Id and customer number combinations:

29088396.png
0
 
Nitin SontakkeDeveloperCommented:
Unfortunately, you cannot have it both ways. If you order by the last update date desc, then your customers will not be in order. Is that something which is acceptable?

If yes, just change your order by clause as follows:

ORDER BY a.updt_ts DESC,
  b.cust_id,
  b.cust_nm,

Open in new window

0
 
venkatesh SarivisettySenior Software EngineerAuthor Commented:
Last 10th row should come beginning right as it is 08 March??
0
 
Nitin SontakkeDeveloperCommented:
Yes, but ONLY IF you ignore customer id and customer number columns. Right? But you didn't tell that to the query. So try the order by given by me above. BUT then remember your customer ids and names will be in order WITHIN descending order or dates.

I believe the out you are getting is the most sensible one (within a particular context, of course!)

What information you (i.e. your manager / customer) are trying to fetch and to make exactly what business decision? This is very important for the correct order by clause. May be just consult them.
0
 
venkatesh SarivisettySenior Software EngineerAuthor Commented:
Now it is working as expected now, I am seeing the timestamp in an order..

Now I have another requirement to pull the unique consumers list and that query is working as expected. But in this query also I am not seeing the customer name in an order. Could you please help me out on that..

SELECT DISTINCT first_nm,last_nm,cust_id,cust_nm
    FROM
    (SELECT b.cust_id,
    b.cust_nm,
    a.first_nm,
    a.last_nm,
    a.middle_nm,
    a.updt_ts,
    SUM(DECODE(a.status_cd,'pending',1,0))  AS Pending,
    SUM(DECODE(a.status_cd,'Error',1,0))    AS Error,
    SUM(DECODE(a.status_cd,'success',1,0))  AS success,
    SUM(DECODE(a.status_cd,'existing',1,0)) AS Existing,
    SUM(DECODE(a.status_cd,'canceled',1,0)) AS Cancelled,
    SUM(DECODE(a.status_cd,'',1,0))         AS Null_Values
    FROM table a,
    PRODAUTH_DB.CHEX_CUST b
    WHERE A.UPDT_TS BETWEEN TO_DATE('28-JUN-2017 00:00:00','DD-MON-YYYY HH24:MI:SS') AND sysdate
    AND b.cust_id=a.cust_id
    GROUP BY b.cust_id,
    b.cust_nm,
    a.first_nm,
    a.last_nm,
    a.middle_nm,
    a.updt_ts
    ORDER BY b.cust_nm DESC,
    b.cust_id,a.updt_ts
  );

Result,
12345678      ABC BANK
      34563456      XYZ BANK
      56789034      GAYA BANK
      56789034      GAYA BANK
      56789034      GAYA BANK
      76584736      ALL BANK
      30280246      CREDIT BANK
      76584736      ALL BANK
      76584736      ALL BANK

Need to arrange the bank names in an order .. Pelase suggest
0
 
venkatesh SarivisettySenior Software EngineerAuthor Commented:
Thank you so much for your support. My issue has been resolved now.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.