QUERY HELP NEEDED

fwd...@gmail.com

unread,

Mar 10, 2007, 9:04:48 AM3/10/07

to

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

Michel Cadot

unread,

Mar 10, 2007, 10:19:51 AM3/10/07

to

<fwd...@gmail.com> a écrit dans le message de news: 1173535488.8...@j27g2000cwj.googlegroups.com...

select * from (

SELECT COUNT(TABLE1.COL1) AS Expr1, TABLE2.COL2
FROM TABLE1,TABLE2 where TABLE1.COL1 = TABLE2.COL1
GROUP BY TABLE2.COL2
ORDER BY expr1 DESC

) where rownum = 1

Regards
Michel Cadot

fitzj...@cox.net

unread,

Mar 10, 2007, 11:05:59 AM3/10/07

to

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

Michel Cadot

unread,

Mar 10, 2007, 12:08:29 PM3/10/07

to

<fitzj...@cox.net> a écrit dans le message de news: 1173542759....@h3g2000cwc.googlegroups.com...

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

fitzj...@cox.net

unread,

Mar 10, 2007, 1:05:54 PM3/10/07

to

On Mar 10, 11:08 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> <fitzjarr...@cox.net> a écrit dans le message de news: 1173542759.323573.17...@h3g2000cwc.googlegroups.com...

Nicely done.

David Fitzjarrell

fwd...@gmail.com

unread,

Mar 12, 2007, 1:41:00 AM3/12/07

to

Hi
sorry for the late reply. One thing i forget to mention is that i am
using MS SQL Server 2000 and when i use max(count(column_name)) it
gives me error msg CANNOT PERFORM AN AGGREGATE FUNCTION ON AN
EXPRESSION CONTAINING AN AGGREGATE OR A SUBQUERY and also there is no
RANK() function available.

fwd...@gmail.com

unread,

Mar 12, 2007, 1:57:16 AM3/12/07

to

PROB SOLVED. with some minor changes in David's query i m able to
solve the problem. This is the query i m using

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 TOP 1 COUNT(TABLE1.COL1) As Expr1

FROM TABLE1,TABLE2 where TABLE1.COL1 = TABLE2.COL1
GROUP BY TABLE2.COL2

ORDER BY Expr1 desc);

also tell me if there are any other alternatives i can use. and Thanku
all of u very much.

fitzj...@cox.net

unread,

Mar 12, 2007, 11:18:26 AM3/12/07

to

If you're using SQL Server why on EARTH are you posting this question
HERE???
You SHOULD be posting in comp.databases.sql-server.

David Fitzjarrell

DA Morgan

unread,

Mar 12, 2007, 6:23:32 PM3/12/07

to

Very simply:
Oracle <> SQL Server

Go to:
http://msdn2.microsoft.com/en-us/library/ms203721.aspx
--
Daniel A. Morgan
University of Washington
damo...@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org