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.
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 
