Group Consecutively and sort using date in SQL

Question

I am trying to sort this data using SQL Query

Sort the data using start_date descending first - this is to keep latest transactions first.
Sort the data based on COL_A, COL_B, COL_C such that COL_A, COL_B and COL_C data are grouped consecutively for any given set of records.

I tried using ROW_NUMBER and DENSE_RANK functions - but unable to derive. Could any one please help me on this?

Table data:

COL_A	COL_B	COL_C	TASK	START_DATE	STATUS
REFERENCE	GOLD	ETL	Upload	2022-08-04 16:40:17.000	Completed
REFERENCE	GOLD	ETL	Translate	2022-08-04 16:36:33.000	Completed
REFERENCE	GOLD	ETL	Extract	2022-08-04 16:21:41.000	Completed
Hive	BRONZE	WORKFLOW	Translate	2022-08-04 12:30:25.000	Failed
Hive	DM	ETL	Extract	2022-08-04 12:23:55.000	Completed
Hive	BRONZE	WORKFLOW	Extract	2022-08-04 12:15:44.000	Completed
Standalone	CONS	ETL	Extract	2022-08-04 07:17:31.000	Failed
Moving Window	AGG	ETL	Upload	2022-08-03 15:08:48.000	Completed
Moving Window	AGG	ETL	Translate	2022-08-03 15:05:41.000	Completed
Moving Window	AGG	ETL	Extract	2022-08-03 14:53:50.000	Completed
Moving Window	ANLT	ETL	Upload	2022-08-03 14:31:17.000	Completed
Moving Window	ANLT	ETL	Translate	2022-08-03 14:26:17.000	Completed
Moving Window	ANLT	ETL	Extract	2022-08-03 14:17:50.000	Completed
Hive	BRONZE	BILL	Translate	2022-08-03 13:46:19.000	Completed
Standalone	CONS	ETL	Extract	2022-08-03 13:34:09.000	Failed

Expected Output:

COL_A	COL_B	COL_C	TASK	START_DATE	STATUS
REFERENCE	GOLD	ETL	Upload	2022-08-04 16:40:17.000	Completed
REFERENCE	GOLD	ETL	Translate	2022-08-04 16:36:33.000	Completed
REFERENCE	GOLD	ETL	Extract	2022-08-04 16:21:41.000	Completed
Hive	BRONZE	WORKFLOW	Translate	2022-08-04 12:30:25.000	Failed
Hive	BRONZE	WORKFLOW	Extract	2022-08-04 12:15:44.000	Completed
Hive	DM	ETL	Extract	2022-08-04 12:23:55.000	Completed
Standalone	CONS	ETL	Extract	2022-08-04 07:17:31.000	Failed
Moving Window	AGG	ETL	Upload	2022-08-03 15:08:48.000	Completed
Moving Window	AGG	ETL	Translate	2022-08-03 15:05:41.000	Completed
Moving Window	AGG	ETL	Extract	2022-08-03 14:53:50.000	Completed
Moving Window	ANLT	ETL	Upload	2022-08-03 14:31:17.000	Completed
Moving Window	ANLT	ETL	Translate	2022-08-03 14:26:17.000	Completed
Moving Window	ANLT	ETL	Extract	2022-08-03 14:17:50.000	Completed
Hive	BRONZE	BILL	Translate	2022-08-03 13:46:19.000	Completed
Standalone	CONS	ETL	Extract	2022-08-03 13:34:09.000	Failed

You wrote that you've tried something, but there's no any line of code in the question. It is a simple `order by` by 4 columns. Your sample data has all distinct timestamps, so the logic of sorting other column is not clear (or at least cannot be clearly demonstrated using this sample data). — astentx, Aug 11 '22 at 21:50

fernan · Answer 1 · 2022-08-11T17:12:29.750

0

have you tried something along the lines of this SQL query?

SELECT COL_A, COL_B, COL_C, TASK, START_DATE, STATUS
FROM table
ORDER BY START_DATE DESC, COL_A, COL_B, COL_C;

If you want to also summarize somehow the results you could do something like this to e.g. count the number of unique instances of a given column.

-- for col_a
SELECT COUNT(DISTINCT(COL_A)) AS number, COL_A
FROM table
GROUP BY COL_A, COL_B, COL_C
ORDER BY START_DATE DESC;

-- for col_b
SELECT COUNT(DISTINCT(COL_B)) AS number, COL_B
FROM table
GROUP BY COL_A, COL_B, COL_C
ORDER BY START_DATE DESC;

HTH,

See also these posts: Using GROUP BY on multiple columns ORDER BY on multiple columns

edited Aug 11 '22 at 17:12

answered Aug 11 '22 at 14:22

fernan

36
6

The moment I add START_DATE in order by clause, sort order gets collapsed. – LNC Aug 11 '22 at 14:41
OK, yes, I got confused by your use of "grouped" in your question and hence I proposed the use of the GROUP BY SQL modifier. In this case yes, the GROUP BY would collapse rows, if they have the same contents (e.g. the first three rows in your example if you only look at cols a, b and c. – fernan Aug 11 '22 at 16:52
I guess you'd want to ORDER BY rows based on multiple criteria, e.g. 'ORDER BY start_date DESC, col_a, col_b, col_c'. – fernan Aug 11 '22 at 16:55
Also your original + expected tables are the same! – fernan Aug 11 '22 at 17:00

score 0 · Answer 2 · edited Aug 12 '22 at 06:25

With sample data as above and with first order by START_DATE DESC any other ordering does not do anything because all the start date data are different so there is no need for additional orderings.

But in case there are some rows with exactly the same START_DATE then it is another story.

If you want to do such additional orderings using VarChar2 columns data then, I'm afraid, you will need a little help from some cte-s that will help you make that sorting work. That includes explicit definition of the orderings for every possible VarChar2 value in the columns you want to order by. I don't know how many of those values you have in your table but it would look like below (for the data provided):

    order_a AS
        (
            Select  0 "ORD", 'REFERENCE'     "COL_X" From Dual Union All
            Select 10 "ORD", 'Hive'          "COL_X" From Dual Union All
            Select 20 "ORD", 'Standalone'    "COL_X" From Dual Union All
            Select 30 "ORD", 'Moving Window' "COL_X" From Dual 
        ),
    order_b AS
        (
            Select  0 "ORD", 'GOLD'     "COL_X" From Dual Union All
            Select 10 "ORD", 'SILVER'   "COL_X" From Dual Union All
            Select 20 "ORD", 'BRONZE'   "COL_X" From Dual Union All
            Select 30 "ORD", 'DM'       "COL_X" From Dual Union All
            Select 40 "ORD", 'CONS'     "COL_X" From Dual Union All
            Select 50 "ORD", 'AGG'      "COL_X" From Dual Union All
            Select 60 "ORD", 'ANLT'     "COL_X" From Dual 
        ),
    order_c AS
        (
            Select  0 "ORD", 'ETL'      "COL_X" From Dual Union All
            Select 10 "ORD", 'WORKFLOW' "COL_X" From Dual Union All
            Select 20 "ORD", 'BILL'     "COL_X" From Dual 
        )
SELECT t.COL_A, t.COL_B, t.COL_C, t.TASK,
    To_Char(t.START_DATE, 'yyyy-mm-dd hh24:mi:ss') "START_DATE",
    t.STATUS
FROM 
    tbl t
LEFT JOIN
    order_a a ON(a.COL_X = t.COL_A)
LEFT JOIN
    order_b b ON(b.COL_X = t.COL_B)
LEFT JOIN
    order_c c ON(c.COL_X = t.COL_C)
ORDER BY
    t.START_DATE DESC, Nvl(a.ORD, 999), Nvl(b.ORD, 999), Nvl(c.ORD, 999)

Once more, there is no need to do this if all START_DATE values are different. Regards...

@marc_s Thank you for your help. English is not my native language and every correction is appreciated. Thanks... — d r, Aug 12 '22 at 09:57

Group Consecutively and sort using date in SQL

2 Answers2