Re: QUERY HELP NEEDED

<fitzjarrell_at_cox.net> a écrit dans le message de news: 1173542759.323573.17160_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 Received on Sat Mar 10 2007 - 11:08:29 CST