i have a table like sales....the field are
Prodid Jan(jam month sales)Feb March
1 20 76 50
2 30 94 40
3 40 90 30
4 70 20 30
5 23 40 40
6 85 30 55
7 84 20 65
8 10 93 40
9 57 30 30
10 38 83 40
11 35 39 90
12 83 89 50
Now the Question is i want get the max sales of 12 products
from the months.hint:for eg I WANT GET 89 for product12...
Can any one help me
- 8 Answers
- 9071 Views
- Satyam, I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
Answer / guest
Thnks for ur rly.......
But if the table is like this
i have a table like sales....the field are
Prodid Jan(jam month sales)Feb March apr may jun july
1 20 76 50 74 94 93 83
2 30 94 40 94 93 93 02
3 40 90 30 49 94 92 56
4 70 20 30 93 46 35 78
5 23 40 40 39 84 46 57
6 85 30 55 93 46 46 46
7 84 20 65 83 56 57 57
8 10 93 40 93 68 57 36
9 57 30 30 83 67 57 79
10 38 83 40 83 55 68 47
11 35 39 90 94 57 78 47
12 83 89 50 93 79 69 69
Actualy data is up to dec month...
If u know the ans plz let me know
| Is This Answer Correct ? | 1 Yes | 0 No |
Answer / narasimha
SEL PROD_ID, GREATEST(JAN,FEB,MAR,APR) AS MAX_SALE FROM TABLE_NAME
QUALIFY ROW_NUMBER() OVER (ORDER BY MAX_SALE) =1;
| Is This Answer Correct ? | 1 Yes | 0 No |
select case when jan>feb and jan>mar and jan> apr and jan>
may and jan>jun then
jan else (case when feb>jan and feb>mar and feb>apr and
feb>may and feb>jun then
feb else (case when mar>jan and mar>feb and mar>apr and
mar>may and mar>jun then
mar else (case when apr>jan and apr>feb and apr>mar and
apr>may and apr>jun then
apr else (case when may>jan and may>feb and may>apr and
may>mar and may>jun then
may else (case when jun>jan and jun>feb and jun>mar and
jun>apr and jun>may then
jun end)end)end)end)end)end from sales;
upto dec we can use similar query to get the max sales from
that sales table i hope u undertand if u have any queries
reach me ravikiran.mca.vadali@gmail.com
| Is This Answer Correct ? | 0 Yes | 0 No |
Answer / guest
Thanks raju......
But i want in a simple manner..
if there is any other way plz let mi know
thanks in advance
| Is This Answer Correct ? | 0 Yes | 0 No |
Answer / pavan
sel case
when m1-m2 >0 and m1-m3 >0 then m1
when m2-m1>0 and m2-m3>0 then m2
when m3-m1>0 and m3-m2 >0 then m3
end as maxmaks,m1,m2,m3,id from ttemp.t3;
| Is This Answer Correct ? | 0 Yes | 0 No |
Answer / naga
sel t.p,t.jan1, t.col_nm
from (
select p,jan1,'jan1' as col_nm from pm1
union
select p,feb1,'feb1' from pm1
union
select p,mar,'mar' from pm1
)t
qualify rank () over(partition by p order by jan1 desc) =1 order by p;
its bit lengthy but not a complex one..I just
added extra col as col_nm to identify the month name
| Is This Answer Correct ? | 0 Yes | 0 No |
What are differences between teradata and ansi session modes in teradata?
The transaction exceeded the maximum number of rowhash locks allowed
What is the command in bteq to check for session settings ?
How to run the Teradata jobs in unix environment ???
Describe the between keyword in teradata?
How to load specific no.of records using bteq, or fastload,or multiload
What is upsert statement in teradata?
What is teradata and why it is used?
Difference between inner join and outer join?
in ur table contains the coloumns like deptno,sal empname then i want output as deptno subtotalofdept totalsal 10 3700 3700 20 3400 7100 like that
What is the purpose of joins in teradata?
i have a table like sales....the field are Prodid Jan(jam month sales)Feb March 1 20 76 50 2 30 94 40 3 40 90 30 4 70 20 30 5 23 40 40 6 85 30 55 7 84 20 65 8 10 93 40 9 57 30 30 10 38 83 40 11 35 39 90 12 83 89 50 Now the Question is i want get the max sales of 12 products from the months.hint:for eg I WANT GET 89 for product12... Can any one help me
Teradata 
