|
View previous topic :: View next topic
|
| Author |
Message |
prasadplease
New User
Joined: 02 Sep 2006
Posts: 31
Location: Mumbai
|
|
|
|
Hi,
My table has 5 similar columns.
For each record in table, I want the data of the column having the max value.
E.g.
Row 1 : ABC 5 7 8 3 1
Row 2 : PQR 9 4 0 3 5
Output
ABC 8
PQR 9
Has anyone come across such situation, where you need to compare columns of same table.
Thanks,
Prasad. |
|
| Back to top |
|
 |
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| Code: |
| select col1,max(col2,col3,col3,col4) from table1 |
should work |
|
| Back to top |
|
|
prasadplease
New User
Joined: 02 Sep 2006
Posts: 31
Location: Mumbai
|
|
|
|
Wow!!!
I really had no clue that max also works on columns....
Thanks
GuyC
Now next problem is if any column has nulls it's value is returned as max... |
|
| Back to top |
|
|
Bharath Bhat
Active User
Joined: 20 Mar 2008
Posts: 283
Location: chennai
|
|
|
|
| Code: |
| select col1,max(coalesce(col2,0),col3,col3,col4) from table1 |
I've assumed only col2 to be nullable. You can do the same for other columns as well. |
|
| Back to top |
|
|
prasadplease
New User
Joined: 02 Sep 2006
Posts: 31
Location: Mumbai
|
|
|
|
| Thanks Bharath, GuyC |
|
| Back to top |
|
|
lkhiger
New User
Joined: 28 Oct 2005
Posts: 89
|
|
|
|
| Bharath Bhat wrote: |
| Code: |
| select col1,max(coalesce(col2,0),col3,col3,col4) from table1 |
I've assumed only col2 to be nullable. You can do the same for other columns as well. |
You had used Max(c1, c2, ...cj) - which is scalar function,
but you may get duplicates.
So, to this function you have to use MAX - column function.
Solution could be:
| Code: |
select tabid, MAX(Max(c1, c2, ...cj) )
from table1
group by tabid |
Thanks, Lenny. |
|
| Back to top |
|
|
lkhiger
New User
Joined: 28 Oct 2005
Posts: 89
|
|
|
|
How I see, I have master degree on troubles.... 
Better to use next SQL:
| Code: |
Select tabid, Max(MaxC ) MMaxC
from
(select tabid, Max(c1, c2, ...cj) MaxC
from table1 ) mx
group by tabid |
Lenny |
|
| Back to top |
|
|
|
|
