Subquery and Order By 1

[jhall01]

I have some data I need sorted and then count the sequence and then load into a table. Here is my process order that isn't working:

DROP TABLE temp;

CREATE TABLE temp

INSERT INTO temp
SELECT
a,
b,
c,
d,
CSUM(1,1) as SEQ_count
...
GROUP BY a, b, c
ORDER BY a, b, c, d desc

It tells me I can't use ORDER BY in a Sub query. I have to prioritize my data (which is not in any good order) and set it to a value to Rotate at a later date by priority:

ie a, b, c, dpriority1, dpriority2, dpriority3

Is there another way to do different sorts depending on which column i want to prioritize or if i want to prioritize more than 1 column. Can I set a default order by on the CREATE TABLE and then do the CSUM(1,1) after data is inserted?

Thanks in advance!!

Jon

 

[jhall01]

Here is a detailed layout of the data

This is what the Query displays:

custID product value datesold
1111 xyz 20.00 1/1/2003
1111 abc 10.00 1/8/2003
2222 xyz 5.00 3/2/2003
2222 abc 10.00 3/2/2003
2222 cde 1.00 3/5/2003
3333 pqr 99.00 1/1/2003

Now I want rotate it to look like this:

custID 1stProduct 2ndProduct 3rdProduct
111 xyz abc
222 xyz abc cde
333 pqr


but i only want to display 3 products per custID sometime based on latest date and sometimes based on highest value.

A customer can have more than 3 products and there are over 100 different products.

In other words. For each customer i want to rotate the highest valued products from highest to lowest 1stProduct being the highest Value or vice versa

or

by date, 1stProduct being the latest datesold

originally i would do a CSUM(1,1) Sequence but and translate via Sequence, but when i try to ORDER by for highest value or latest date and try to translate it gives me the error of ORDER BY not allowed in subquery.

Hope this helps in giving you more detail. Thanks Again

 

[Patten]

Try a Csum over multiple columns such as csum(1,product)
with a having clause csum(1,product) < 4 to have only the first 3 and take a look at the qualify function. I'm sorry not to give you the approppiate syntax since I do not have the manuals with me, but that's the way to go for it: with a having clause and the qualify function: at least that's how I would try to solve it.
Good luck

 

[dnoeth]

This is a typical crosstab query transposing rows to columns.
There's no need to use a temporary table, you can calculate it on the fly: As Patten said, you need to calculate a rownumber within a derived table and then you aggregate using CASE.

sel
custid
,max(case when rnk = 1 then product else '' end) as product1
,max(case when rnk = 2 then product else '' end) as product2
,max(case when rnk = 3 then product else '' end) as product3
from
(
sel
custID
,product
,sum(1) over (partition by custid order by &quot;value&quot; desc
rows unbounded preceding) as rnk
/*** in V2R5 you can use
row_number() over (partition by custid order by &quot;value&quot; desc) as rnk
***/
from
mytable
qualify rnk <= 3
) dt
group by 1
order by 1
;

Dieter

 

[jhall01]

This helps alot! Thanks, I have one problem, I still need to show the sold date in this example. I did not ask that above cause I was using a non-aggregate function to do my ranking so I didn't need to worry about grouping.

Now since your example uses the aggregate function sum() i can't group by custID, product, dateSold because it won't rank because each record is a unique instance due to date.

I need to transform into this pattern

CUSTID product1 date1 product2 date2 product3 date3

Any Ideas?

 

[jhall01]

Nevermind. I got it I had to look in the Teradata manuals to understand the code you gave me deiter. It works perfectly except I tried it with RANK() instead of SUM(1) Both work perfectly. I did have an issue with grouping but figured out the Partition By does the grouping.

Thanks Again!!!!