Home » SQL & PL/SQL » SQL & PL/SQL » SQL query help! group...
icon8.gif  SQL query help! group... [message #235391] Sat, 05 May 2007 08:27 Go to next message
760613
Messages: 16
Registered: March 2005
Junior Member
Hi,

could someone please help me build this query...



LogonLogTbl
------------

ID..........USERID........IP............LOGDATE
1...........1000..........a.b.c.d.......2007-05-01...
2...........1001..........a.b.c.d.......2007-05-01...
3...........1203..........w.x.y.z.......2007-05-01...
4...........1203..........w.x.y.z.......2007-05-02...
5...........1000..........a.b.c.d.......2007-05-02...
etc


Well, this is our logon log and I want to see if someone logs from same IP but different userid (sort by date)...

i.e.
My output after the sql query would be... (it is not important to show all logons, just enough to show it once.. )


ID USERID IP DATE
1 1000 a.b.c.d 2007-05-0...
2 1001 a.b.c.d 2007-05-0...
etc


(no need to show e.g. id 5 - it is already covered by 1)



Is this possible ?

Plase help me out.


thank you in advance!!!!
Re: SQL query help! group... [message #235395 is a reply to message #235391] Sat, 05 May 2007 08:42 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Is this possible ?
yes, by having the table twice in the FROM clause.
Re: SQL query help! group... [message #235397 is a reply to message #235395] Sat, 05 May 2007 08:46 Go to previous messageGo to next message
760613
Messages: 16
Registered: March 2005
Junior Member
Thank you Anacedent.

Im not getting further than this query, and Im stucked... Sad



select *
from LogonLogTbl
where IP in
( select IP
from LogonLogTbl
group
by IP
having count(*) > 1
)
order by IP


But this show all not only those that got different userid and same ip. hmmmm


please advice.
Re: SQL query help! group... [message #235398 is a reply to message #235391] Sat, 05 May 2007 08:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

select id, userid, ip, "DATE"
from (
select tab.*,
       count(distinct userid) over (partition by ip) cnt
from tab
)
where cnt > 1
order by "DATE", userid
/

I don't see why don't want 5 but you want 1? Why not keeping 5 and not 1.
If "DATE" is relevant you should keep all rows (this is what I gave).

Regards
Michel
Re: SQL query help! group... [message #235399 is a reply to message #235391] Sat, 05 May 2007 08:49 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
What part of "by having the table twice in the FROM clause." do you NOT understand?

FROM LogonLogTbl T1, LogonLogTbl T2
Re: SQL query help! group... [message #235402 is a reply to message #235391] Sat, 05 May 2007 09:01 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
select id, userid, ip, logdate 
from(
select distinct userid, id, ip, logdate
from logonlogtb1 a, logonlogtb1 b
where a.ip=b.ip
and a.userid<>b.userid)
order by logdate desc;
Re: SQL query help! group... [message #235404 is a reply to message #235391] Sat, 05 May 2007 09:13 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
Michel's solution is very elegant, but it makes use of analytics--which may not be every body's cup of tea.
Re: SQL query help! group... [message #235405 is a reply to message #235404] Sat, 05 May 2007 09:17 Go to previous message
760613
Messages: 16
Registered: March 2005
Junior Member
Great thanks Michel and Saibal.

You saved my day!!! Smile
Previous Topic: retrieve a column content
Next Topic: decode
Goto Forum:
  


Current Time: Sat Dec 10 09:15:40 CST 2016

Total time taken to generate the page: 0.28512 seconds