Please kindly help. How do I get the top 3 in derived table?

Analytics

Please kindly help. How do I get the top 3 in derived table?

Hello,

Please kindly help. I am trying to get the top 3 incomes but my query is not working. I am new to SQL and not sure what I am doing wrong.

Thank you in advance,

Christine

SELECT msa_new.store AS store, msa_new.city AS city, msa_new.state AS state, SUM(trns_new.revenue) AS revenue, SUM(trns_new.numdays) AS numdays, SUM(trns_new.revenue)/SUM(trns_new.numdays) AS Avg_rev, msa_new.income AS maxincome
FROM (SELECT TOP 3 MAX(s.msa_income) AS income, s.store, s.city, s.state FROM store_msa s GROUP BY s.store, s.city, s.state ) AS msa_new 
JOIN (SELECT t.store, COUNT(DISTINCT t.saledate) AS numdays, SUM(t.amt) AS revenue, EXTRACT(YEAR from t.saledate) AS year_num, EXTRACT(MONTH from t.saledate) AS month_num,(CASE WHEN year_num= '2005' AND month_num= '8' THEN 'exclude' ELSE 'include' END) AS Filter FROM trnsact t WHERE t.stype='p' AND Filter='include' GROUP BY t.store, year_num, month_num 
HAVING COUNT(DISTINCT t.saledate)>=20 )AS trns_new ON trns_new.store=msa_new.store 
GROUP BY msa_new.store, msa_new.income, msa_new.city, msa_new.state
ORDER BY msa_new.income DESC;

 


Accepted Solutions
Teradata Employee

Re: Please kindly help. How do I get the top 3 in derived table?

Christine,

 

Two suggestions based on what your input data is looking like. Both use the fact that TOP will return the first rows, so we order by income so that only the top 3 incomes are returned.

 

  • If there are multiple rows for each combination of store, city, and state and you want the top 3 Max's then use:
SELECT top 3 store, city, state, MAX(income)
FROM store_msa
GROUP BY store, city, state
ORDER BY income desc
  • If there is a single row for each combination of store, city, and state use:

 

SELECT top 3 store, city, state, income
FROM store_msa
ORDER BY income desc

 

 

If neither of these work please provide some input data and we'll try to help more. Good luck!

Michelle

1 ACCEPTED SOLUTION
2 REPLIES
Teradata Employee

Re: Please kindly help. How do I get the top 3 in derived table?

Christine,

 

Two suggestions based on what your input data is looking like. Both use the fact that TOP will return the first rows, so we order by income so that only the top 3 incomes are returned.

 

  • If there are multiple rows for each combination of store, city, and state and you want the top 3 Max's then use:
SELECT top 3 store, city, state, MAX(income)
FROM store_msa
GROUP BY store, city, state
ORDER BY income desc
  • If there is a single row for each combination of store, city, and state use:

 

SELECT top 3 store, city, state, income
FROM store_msa
ORDER BY income desc

 

 

If neither of these work please provide some input data and we'll try to help more. Good luck!

Michelle

Re: Please kindly help. How do I get the top 3 in derived table?

Thank you Michelle, I just put top 3 in the beginning and grouped as you adviced (but had to add income in my query as it was not aggregated) , incredible.