Re: QUERY HELP NEEDED

On Mar 10, 8:04 am, "fwdk..._at_gmail.com" <fwdk..._at_gmail.com> wrote:
> Hi All,
> i need ur help this is the query i have written now what i want is the
> maximum value from EXPR1 column i try to use use MAX(Count(COL1)) but
> it give error pls help me to achieve this task. i hope u understand my
> question. thanku.
>
> SELECT COUNT(TABLE1.COL1) AS Expr1, TABLE2.COL2
> FROM TABLE1,TABLE2 where TABLE1.COL1 = TABLE2.COL1
> GROUP BY TABLE2.COL2
> ORDER BY expr1 DESC
>
> RESULT
>
> EXPR1 COL2
> 6 1212131
> 6 1212131
> 4 121121
> 2 1212121
>
> what i want is the maximum values
>
> RESULT
> EXPR1 COL2
> 6 1212131
> 6 1212131

select * from
(SELECT COUNT(TABLE1.COL1) AS Expr1, TABLE2.COL2 FROM TABLE1,TABLE2 where TABLE1.COL1 = TABLE2.COL1 GROUP BY TABLE2.COL2)
where expr1 = ( SELECT max(COUNT(TABLE1.COL1)) FROM TABLE1,TABLE2 where TABLE1.COL1 = TABLE2.COL1 GROUP BY TABLE2.COL2); should return the result set you want. Michel's suggestion returns only one row, no matter how many may match the max(expr1) value in the output. This should return all matching rows to that maximum value. Yes, it calls the joined query twice, but to return what you want you need to compare expr1 to the max value for that column ( which you don't return in your main query) to return all matching rows.

Of course, Jonathan Lewis may have a more elegant solution.

David Fitzjarrell Received on Sat Mar 10 2007 - 10:05:59 CST