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
