Greatest Date group by TCP address

Question

What I want: I'm having problems with a greatest-n-per-group problem. My group is a set of TCP Addresses and the n is the date at which the table row was inserted into the database.

The problem: I'm currently getting all rows with tcp Addresses which match my where clause, rather then one with the largest date per tcp address.

I'm trying to follow this example and failing: SQL Select only rows with Max Value on a Column.

Here's what my table looks like.

CREATE TABLE IF NOT EXISTS `xactions` (
  `id` int(15) NOT NULL AUTO_INCREMENT,
  `tcpAddress` varchar(40) NOT NULL,
   //a whole lot of other stuff in batween
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=150 ;

Example rows are

ID | tcpAddress     | ...  | date
1  |  192.168.1.161 | ...  | 2012-09-12 14:19:39
2  |  192.168.1.162 | ...  | 2012-09-12 14:19:40
3  |  192.168.1.162 | ...  | 2012-09-12 14:19:41
4  |  192.168.1.162 | ...  | 2012-09-12 14:19:42

SQL statement I'm trying to use

select yt.id, yt.tcpAddress, yt.analog, yt.discrete, yt.counter, yt.date
from xactions yt
inner join(
    select id, tcpAddress, analog, discrete, counter, max(date) date
    from xactions
    WHERE tcpAddress='192.168.1.161' OR tcpAddress='192.168.1.162'
    group by date
) ss on yt.id = ss.id and yt.date= ss.date

score 1 · Accepted Answer · answered Mar 14 '13 at 21:54

You need to group by the tcpAddress, not by the date.

And join by the tcpAddress, not the id.

select yt.id, yt.tcpAddress, yt.analog, yt.discrete, yt.counter, yt.date 
from xactions yt 
inner join ( 
  select tcpAddress, max(date) date 
  from xactions 
  where tcpAddress in ('192.168.1.161', '192.168.1.162')
  group by tcpAddress
 ) ss using (tcpAddress, date);

Also, you don't need to select any extra columns in the derived table -- only the tcpAddress and the max(date).

This worked for me. I was wondering Why I didn't have to select the extra columns from the derived table. — AlexLordThorsen, Mar 14 '13 at 21:57

Aleksandr Fedorenko · Answer 2 · 2013-03-14T22:36:53.093

1

Also you can use option with EXISTS(). In EXISTS() find MAX(date) for each group of tcpAddress and compare them

SELECT id, tcpAddress, analog, discrete, counter, date
FROM xactions x1    
WHERE EXISTS (
              SELECT 1
              FROM xactions x2
              WHERE x1.tcpAddress = x2.tcpAddress
              HAVING MAX(x2.date) = x1.date
              ) AND (tcpAddress='192.168.1.161' OR tcpAddress='192.168.1.162')

edited Mar 14 '13 at 22:36

answered Mar 14 '13 at 21:55

Aleksandr Fedorenko

16,594
6
37
44

First of all, thanks for taking the time to answer. phpmyadmin gave me this error. I'm using the latest version of WAMP. "...syntax to use near 'AND EXISTS ( SELECT 1 FROM xactions x2 ' at line 3" – AlexLordThorsen Mar 14 '13 at 22:01
So it doesn't have an error anymore but It seems to be returning all rows with tcpAddress = 192.168.1.161 and 192.168.1.162 – AlexLordThorsen Mar 14 '13 at 22:25
sorry I forgot brackets for OR expression;) Try now please – Aleksandr Fedorenko Mar 14 '13 at 22:36

Greatest Date group by TCP address

2 Answers2