select issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

select issue

Author

Topic

zubair
Yak Posting Veteran

67 Posts

Posted - 2009-09-02 : 07:16:23

Hi I have the following data set

IP ORGANISATION SESSION_ID ARRIVAL_TIME
===================================================================
89.167.254.22 Packetexchange 50224930 2009-09-02 11:40:00.0
89.167.254.22 Packetexchange 50224654 2009-09-01 09:50:00.0
81.144.203.173 Morley 54545453 2009-09-01 08:54:00.0
90.197.97.159 Doremus 45345345 2009-09-01 08:54:00.0
89.167.254.22 Packetexchange 23243234 2009-09-01 07:40:00.0

What i want is to get the unique IP's, a count of how many times each distinct IP appears in the data set along with the most recent details for the latest record for that IP based on the arrival time. Can anyone help?

Thx

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-09-02 : 07:26:09

Try like this..

select t1.ip,count(t1.ip),t2.arrival_time from table t1
inner join( select ip,max(ARRIVAL_TIME) as arrival_time from table group by ip) t2 on
t2.ip=t1.ip
group by t1.ip

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

zubair
Yak Posting Veteran

67 Posts

Posted - 2009-09-02 : 08:34:27

thanks senthil. this helped me a lot.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-09-02 : 08:40:12

quote:
Originally posted by zubair

thanks senthil. this helped me a lot.

Welcome

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-02 : 08:48:35

quote:
Originally posted by senthil_nagore

Try like this..

select t1.ip,count(t1.ip),t2.arrival_time from table t1
inner join( select ip,max(ARRIVAL_TIME) as arrival_time from table group by ip) t2 on
t2.ip=t1.ip
group by t1.ip

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

why group twice? wont this be sufficient?


select t1.ip,t2.ip_count,t2.arrival_time from table t1
inner join( select ip,max(ARRIVAL_TIME) as arrival_time,count(1) AS ip_count from table group by ip) t2 on
t2.ip=t1.ip

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-09-02 : 09:08:59

quote:
Originally posted by visakh16

quote:
Originally posted by senthil_nagore

Try like this..

select t1.ip,count(t1.ip),t2.arrival_time from table t1
inner join( select ip,max(ARRIVAL_TIME) as arrival_time from table group by ip) t2 on
t2.ip=t1.ip
group by t1.ip

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

why group twice? wont this be sufficient?

select t1.ip,t2.ip_count,t2.arrival_time from table t1
inner join( select ip,max(ARRIVAL_TIME) as arrival_time,count(1) AS ip_count from table group by ip) t2 on
t2.ip=t1.ip

Ya its enough!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-02 : 09:10:13

good

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources