Want descending order of data in hive:
Requirement: Our requirement is we want to get pnote column in a way that data for particular invoice number should be in descending order. As we want to get the most recent date record from the top of the array (zeroth element) from pnote column.
Currect outputs:
- selecting concat_dispute_notes
SELECT concat(to_date(t.modified_on),"-",b.named_user,"-",t.notes) as concat_dispute_notes FROM Table
- output of concat_dispute_notes that we are getting.
2023-05-31-LMINCU2-chased for pym plan
2023-08-16-LMINCU2-chased for pym plan
2023-12-07-LMINCU2-chased for pym plan
2024-01-11-LMINCU2-chased for pym plan
2024-01-22-LMINCU2-chased for pym plan
2023-05-16-LMINCU2-chased for pym plan
2023-05-02-LMINCU2-chased for pym plan
2023-03-22-LMINCU2-chased for pym plan
2022-11-22-LMINCU2-chased for pym plan
Time taken: 31.091 seconds, Fetched: 9 row(s)
- selecting pnote from concat_dispute_note
Query-> SELECT concat_ws("... ",collect_set(case when trim(concat_dispute_notes) <>"" then concat_dispute_notes end)) as PNOTE from (SELECT concat(to_date(t.modified_on),"-",b.named_user,"-",t.notes) as concat_dispute_notes FROM Table
- output of pnote that we are getting->
2023-05-31-LMINCU2-chased for pym plan...
2023-08-16-LMINCU2-chased for pym plan...
2023-12-07-LMINCU2-chased for pym plan...
2024-01-11-LMINCU2-chased for pym plan...
2024-01-22-LMINCU2-chased for pym plan...
2023-05-16-LMINCU2-chased for pym plan...
2023-05-02-LMINCU2-chased for pym plan...
2023-03-22-LMINCU2-chased for pym plan...
2022-11-22-LMINCU2-chased for pym plan
Time taken: 41.456 seconds, Fetched: 1 row(s)
**We have tried using order by but not getting required order of output SELECT concat_ws("... ",collect_set(case when trim(concat_dispute_notes) <>"" then concat_dispute_notes end)) as PNOTE from (SELECT concat(to_date(t.modified_on),"-",b.named_user,"-",t.notes) as concat_dispute_notes FROM Table) order by t.modified_on desc) as t2 order by PNOTE;
**output by order by
2023-05-31-LMINCU2-chased for pym plan...
2023-08-16-LMINCU2-chased for pym plan...
2023-12-07-LMINCU2-chased for pym plan...
2024-01-11-LMINCU2-chased for pym plan...
2024-01-22-LMINCU2-chased for pym plan...
2023-05-16-LMINCU2-chased for pym plan...
2023-05-02-LMINCU2-chased for pym plan...
2023-03-22-LMINCU2-chased for pym plan...
2022-11-22-LMINCU2-chased for pym plan
Time taken: 38.52 seconds, Fetched: 1 row(s)
We tried sort_Array:
SELECT concat_ws("...", sort_array(collect_set(case when trim(concat_dispute_notes) <> "" then concat_dispute_notes end))) as PNOTE
FROM (
SELECT concat(to_date(t.modified_on), "-", b.named_user, "-", t.notes) as concat_dispute_notes
FROM Table) AS Tablepnote11;
Output of Sort_Array:
2022-11-22-LMINCU2-chased for pym plan...
2023-03-22-LMINCU2-chased for pym plan...
2023-05-02-LMINCU2-chased for pym plan...
2023-05-16-LMINCU2-chased for pym plan...
2023-05-31-LMINCU2-chased for pym plan...
2023-08-16-LMINCU2-chased for pym plan...
2023-12-07-LMINCU2-chased for pym plan...
2024-01-11-LMINCU2-chased for pym plan...
2024-01-22-LMINCU2-chased for pym plan
```
Time taken: 31.719 seconds, Fetched: 1 row(s)
The output of sort array is Ascending order. We need descending order.
------------------------------------------------------------------------------------------------------------------------------------------
We have also tried sort by, cluster by, reverse sort, sort_array(---,false) but did not get required output.
we have tried all the above tried using Sort_Array which sort in Ascending order, tried ranking, sort_by, cluster_by, order_by, group_by, Reverse_array sort array(,false) but nothing worked to get it in descending order as a single record.