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
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 |
Explain the term 'columns' related to relational database management system?
Why do you get spool space errors? How do trouble-shoot them?
Describe the between keyword in teradata?
How many codd's rules are satisfied by teradata database?
In BTEQ, how are the session-mode parameters being set?
What is the difference between teradata and basic rdbms?
Steps to create a data model?
What are the different softwares used with their functions in teradata?
Highlight a few of the important components of Teradata?
What is the difference between fastload and multiload? Which one is faster?
Diff b/w v2r5 and v2r6 ?
What is called partitioned primary index (ppi)?