Search code examples
sqlteradatateradata-sql-assistant

teradata sql assistant giving me an error when limiting?

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
Solution

  • 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