Ask help regarding to TOP, Rank and Partition

Database
Enthusiast

Ask help regarding to TOP, Rank and Partition

Hi, I am new to Teradatabase and try to adapt my way into it. I am a data analyst not a developer. 

Say I have a database as follow

Table name is TRS

PK

sku   store  price   amt   quantity   ptype                   time1                   time2                              VAT

1        A        12    36         3           Me             16:06:06:13:02        16:06:06:14:01                     .25

2        A         6     42         7           Fe              16:06:06:12:24         16:06:06:13:01                .20

3        B         4     20         5           Fe              16:04:04:11:11         16:04:04:11:20                .25

4        C        10    40         4           Fe               16:05:05:01:11         16:05:05:01:30               .20

(Say 5000 records)

1. I want to check/select the records  *WHERE the price*qiantity <> amt *

How could I USE '*' AS multiple function?   

Is the sentence right?

SELECT *

FROM TRS

WHERE price*quantity <>amt;

2. Which function I could use for return the *time period* between time1 and time2

(do not know how to calculate the time could you forward me all the time calculation function info page )

SELECT sku, (time1 -time2) AS timp, RANK()OVER(ORDER BY timp)

FROM trs; 

3. I want to choose first 10 records (all the fields) has biggest number of  *expense* which is the (amt+amt*VAT)

I tried (only use amt because I do not know how to use *)to combine *TOP and RANK()OVER( ORDER BY) together* and try only *RANK()OVER( ORDER BY)* I got total different 10 lines. regarding the second command, I got the whole list but the top 10 is not the same as the first command, why?

SELECT TOP 10 sku, store, amt, RANK()OVER(PARTICITION BY store ORDER BY amt DESC)

FROM trs;

SELECT sku, store, amt, RANK()OVER(PARTICITION BY store ORDER BY amt DESC)

FROM trs;

I tried to use ORDER BY twice to resolve this issue. The system said I can not use nest ORDER BY

SELECT sku, store, amt, RANK()OVER(PARTICITION BY store ORDER BY amt DESC)

FROM trs

ORDER BY amt;

4. When I use PARTITION BY I always get the warning about I did not select something in an aggregate manner. 

can not recall the exact example but did get the error saying I need to select something 

1 REPLY
Junior Contributor

Re: Ask help regarding to TOP, Rank and Partition

Q1: yes, that's correct, you can easily test that

Q2: if both time1 and time2 have a datatype TIME you can use (time1 -time2) hour to second, this will return an INTERVAL.

Q3: I don't get what you actually trying to do, but there's no need for mixing RANK & TOP,  better use QUALIFY instead:

SELECT sku, store, amt, 
RANK()
OVER(ORDER BY (amt+amt*VAT) DESC) AS rnk
FROM trs
QUALIFY rnk <= 10

Q4: ???