Home » SQL & PL/SQL » SQL & PL/SQL » max count top 4
max count top 4 [message #234692] Wed, 02 May 2007 22:35 Go to next message
rnallamo
Messages: 24
Registered: August 2005
Junior Member
Hi

my criteria is to select top 4 users who has more number of logins into my application.
ex: table layout is something like this

User loginattempt
-----------------------
AAAA 1
AAAA 2
BBBBBB 1
BBBBBB 2
CCCCC 1
CCCCC 2
CCCCC 3
CCCCC 4
CCCCC 5
CCCCC 6
CCCCC 7
BBBBBB 7
BBBBBB 6
BBBBBB 5
BBBBBB 4
BBBBBB 3
AAAAA 3
AAAAA 1
AAAAA 2
AAAAA 3
AAAAA 4
AAAAA 5
AAAAA 6
AAAAA 7
AAAAA 8
AAAAA 9
AAAAA 10
AAAAA 11
AAAAA 12
AAAAA 13
AAAAA 14
AAAAA 15
DDDDD 1
DDDDD 10
DDDDD 15
DDDDD 19
TTTTTT 50
EEEEEE 30
EEEFFF 20
EEEFFF 21

any idea how to deal with this

Thanks in advance....
Re: max count top 4 [message #234703 is a reply to message #234692] Wed, 02 May 2007 23:58 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
you can try something like this:
select * from (select user_loginattempt,
count(user_loginattempt)
 from <table_name> 
group by user_loginattempt order by 2 desc)
where rownum<=4;
Re: max count top 4 [message #234732 is a reply to message #234703] Thu, 03 May 2007 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think you meant:
select * 
from ( select "User", count(loginattempt)
       from <table_name> 
       group by "User"
       order by 2 desc )
where rownum<=4;

But what if several users have the same amount of logins?

(to OP, USER is a reserved word you can't use it as a column name.)

Regards
Michel
Re: max count top 4 [message #234737 is a reply to message #234732] Thu, 03 May 2007 01:22 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
Michel,
It is open to your interpretation:
If several people have the same # of login attempts, then my interpretation is that you pick out four individuals with the highest # of login attempts. Not pick out say, twenty individuals with the highest no. of login attempts. I will be interested to hear what you think about this
Re: max count top 4 [message #234741 is a reply to message #234737] Thu, 03 May 2007 01:43 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
This is not the kind of thing you can ask others. It is part of the specs of your application; no discussion point.
Re: max count top 4 [message #234748 is a reply to message #234737] Thu, 03 May 2007 01:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Saibal,

Actually, it was an open question.
As Frank said, it is part of specifications and more a question to the OP than to you (unless you want to think about how to solve the different options).

Regards
Michel
Re: max count top 4 [message #234776 is a reply to message #234692] Thu, 03 May 2007 02:34 Go to previous message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
Actually, I was interested in seeing how others interpreted this problem. I can come up with three equally reasonable interpretation of the question, none of which might actually return four records!
1) Show me the top four logins--that is find the set of distinct logins, sort them in descending order, take the largest four and give me the names of everyone that makes one of those values.

2)Give me up to four logins out the top logins. If more than or less than four people happen to have the top four logins, there should be no rows returned

3)Sort the logins in descending order. Take the first four rows. If there are less than four rows, then so be it--less than four rows shall be returned.
Previous Topic: sql help
Next Topic: Trigger problem
Goto Forum:
  


Current Time: Sat Dec 10 18:48:05 CST 2016

Total time taken to generate the page: 0.14859 seconds