independently Sort a Column

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Enthusiast

independently Sort a Column

Hi All,

 

I'm relatively new to Teradata - I'm creating a BI chart that takes counts # of scheduled tasks per day vs actual completed per day based on timestamp. I have my graph working by gropuing rows based on date. However, the actual completed time stamps aren't always done in sequential order which produces a graph that jumps all over the place.

 

The ideal solution would be to produce 2 seperate queries (each in accending order - 1 for Scheduled Task and 1 for Actual Completed Task) then plot each query on the same chart. Unfortunately, my BI tool does not support plotting seperate queries on the same chart (I've hit multiple dead ends with this approach).

 

So I am trying to find a way to sort an individual column in a single query result within Teradata - see sample table below + my failed attempts at using OLAP functions to achieve the desired sorting result (I literally just want to copy the sorted Partition values into a column, so close). Does anyone know of a way to achieve the desired result? Thank you in advance!

 

FIRST_VALUE(COMPL_TS) OVER (PARTITION BY WORK_ORDER ORDER BY COMPL_TS ASC ROWS BETWEEN 0 PRECEDING AND UNBOUNDED FOLLOWING) AS TEST

MIN(COMPL_TS) OVER (PARTITION BY WORK_ORDER ORDER BY COMPL_TS ASC ROWS BETWEEN 0 PRECEDING AND UNBOUNDED FOLLOWING) AS TEST

 

WORK_ORDERSCHED_COMPL_TSCOMPL_TSTEST (DESIRED_RESULT)
1114Tuesday, Jul 11, 2017 9:32:00.000 AMFriday, Jul 14, 2017 7:23:32.000 AMFriday, Jul 14, 2017 7:23:32.000 AM
1114Tuesday, Jul 11, 2017 12:04:00.000 PMSunday, Jul 16, 2017 5:13:54.000 AMSaturday, Jul 15, 2017 3:01:39.000 PM
1114Tuesday, Jul 11, 2017 2:36:00.000 PMSaturday, Jul 15, 2017 3:01:39.000 PMSunday, Jul 16, 2017 5:13:54.000 AM
1114Tuesday, Jul 11, 2017 5:08:00.000 PMMonday, Jul 17, 2017 6:56:43.000 AMMonday, Jul 17, 2017 6:56:43.000 AM
1114Wednesday, Jul 12, 2017 6:40:00.000 AMTuesday, Jul 25, 2017 10:57:24.000 AMTuesday, Jul 25, 2017 10:57:24.000 AM

Accepted Solutions
Junior Contributor

Re: independently Sort a Column

Resolving parameters is done by the client, i.e. your BI tool.

Works as expected when submitted in SQL Assistant.

1 ACCEPTED SOLUTION
4 REPLIES
Junior Contributor

Re: independently Sort a Column

I'm not sure if I understood correctlly what you want to do, but it looks like you want both timestamp columns sorted independently, i.e. there's no relation between them in the final result?

 

If this is correct a solution would be a self-join based on ROW_NUMBER:

 

select t1.WORK_ORDER, t1.SCHED_COMPL_TS, t2.COMPL_TS
from
 ( select WORK_ORDER, SCHED_COMPL_TS,
      ROW_NUMBER() OVER (PARTITION BY WORK_ORDER ORDER BY SCHED_COMPL_TS) as rn
   from tab
 ) as t1
join 
 ( select WORK_ORDER, COMPL_TS,
      ROW_NUMBER() OVER (PARTITION BY WORK_ORDER ORDER BY COMPL_TS) as rn
   from tab
 ) as t2
on t1.WORK_ORDER = t2.WORK_ORDER
and t1.rn = t2.rn
order by t1.rn

 

But your BI tool might support charting the result of a UNION:

 

select 'scheduled' as grp, WORK_ORDER, SCHED_COMPL_TS
from tab
UNION ALL
select 'actual' as grp, WORK_ORDER, COMPL_TS
from tab
order by 2,1,3  -- or whatever your BI tool likes/needs

 

 

 

Enthusiast

Re: independently Sort a Column

Your join suggestion worked perfectly!! Thanks so much Dieter (I've read so many of your post - you are a rockstar)! Do have a follow up question - am trying to pass a user defined parameter (via my BI Chart) - I've tried the code below but it keeps requesting 2 parameters, I only want 1 parameter that is fed to both derived tables.

 

Is there an easy way to do this? There doesn't appear to be an easy way to to store the parameter into a global variable? I am using Teradata 15.10.05.03.

 

select t1.WORK_ORDER, t1.SCHED_COMPL_TS, t2.COMPL_TS
from
 ( select WORK_ORDER, SCHED_COMPL_TS,
      ROW_NUMBER() OVER (PARTITION BY WORK_ORDER ORDER BY SCHED_COMPL_TS) as rn
   from tab

  where tab.x = ?Param1
 ) as t1
join 
 ( select WORK_ORDER, COMPL_TS,
      ROW_NUMBER() OVER (PARTITION BY WORK_ORDER ORDER BY COMPL_TS) as rn
   from tab

  where tab.x = ?Param1
 ) as t2
on t1.WORK_ORDER = t2.WORK_ORDER
and t1.rn = t2.rn
order by t1.rn

Junior Contributor

Re: independently Sort a Column

Resolving parameters is done by the client, i.e. your BI tool.

Works as expected when submitted in SQL Assistant.

Enthusiast

Re: independently Sort a Column

Was able to get it to work with your suggestion (i.e. using the client side/BI tool) - thanks again!