Re: QUERY HELP NEEDED

On Mar 10, 11:08 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> <fitzjarr..._at_cox.net> a écrit dans le message de news: 1173542759.323573.17..._at_h3g2000cwc.googlegroups.com...
> | 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
> |
> |
>
> select expr1, col2 from (
> SELECT COUNT(TABLE1.COL1) AS Expr1, TABLE2.COL2,
> rank() over (order by COUNT(TABLE1.COL1) desc) rk
> FROM TABLE1,TABLE2 where TABLE1.COL1 = TABLE2.COL1
> GROUP BY TABLE2.COL2
> ) where rk = 1
> /
>
> Regards
> Michel Cadot

Nicely done.

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