I am new to Teradata and I am having a hard time limiting results. What I am trying to do is I want to get the max number.

Right now, it just giving me an error called SELECT Failed. 3706:. Can anybody please let me know what I am doing wrong here ?

I have another question, I have a filter called onsite = 'Y'. Can anybody please tell me why I have to group by onsite too. Otherwise, my query will not run. Thank you so much for your help. 

SELECT short_sku , Count(item_full_sku) FROM category GROUP BY short_sku, onsite HAVING onsite = 'Y' ORDER BY Count(full_sku) DESC LIMIT 1

2 Answers

You want to use the TOP function:

SELECT TOP 1 short_sku , Count(item_full_sku) FROM category WHERE onsite = 'Y' GROUP BY short_sku ORDER BY Count(full_sku) DESC ;

If you want to filter out rows where onsite='Y' you should move that to a WHERE clause. The HAVING clause is used for filtering out aggregations.

5

Another solution utulizes Analytical Functions, which are more versatile than TOP, e.g. you can get the TOP n per group adding PARTITION BY:

SELECT short_sku , Count(item_full_sku) FROM category WHERE onsite = 'Y' GROUP BY short_sku, onsite QUALIFY row_number() over (ORDER BY Count(full_sku) DESC) = 1

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password

Post as a guest

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.