I have a table which I'm trying to select the minimum values grouped by 2 columns i.e. column 1 and column 6.
Some of the data from the table is the below (just reduced irrelevant parts data down, and omitted additional rows)
C.1 (sID) C.2 C.3 (Standard_Time) C.4 C.5 C.6 (SIP) C.7 C.8 (Unix_Time)
1. (u'8888', u'G', u'2014-08-10 00:23:57', u'At', u'2', u'10.168.77.10', u'X', 1407626637.0)
2. (u'8888', u'G', u'2014-08-10 00:23:58', u'At', u'2', u'10.168.77.10', u'y', 1407626638.0)
3. (u'8888', u'G', u'2014-08-10 00:23:58', u'At', u'2', u'222.222.222.222', u'z', 1407626638.0)
4. (u'8888', u'G', u'2014-12-11 19:19:19', u'At', u'2', u'10.168.77.10', u'A', 1418325559.0)
5. (u'1411', u'SM', u'2014-08-10 00:23:56', u'At', u'2', u'192.168.77.114', u'z', 1407626636.0)
I was hoping to retrieve rows 1, 3, and 5 (as a result of being grouped by column 1 initially, then by column 6; whereby in column 6 there a 3 unique IP addresses. My code is shown below (although I don't really want to return Unix_Time twice, but it also aids here in showing what's going wrong).
minList = []
c.execute('SELECT MAX(Unix_Time), * FROM myTable group by SID, SIP')
ans = c.fetchall()
for r in ans:
minList.append(r)
This code returns -
Rows 3 and 5 as expected, and bizarrely returns row 4 with two different Unix_Times. The first one belongs to row 1 (which is indeed the lowest value so row 1 should really be returned) and the last one belongs to itself i.e. row 4 (the rest of the row is row 4's)
(Both 1 & 4?) (1407626637.0, u'8888', u'G', u'2014-12-11 19:19:19', u'At', u'2', u'10.168.77.10', u'A', 1418325559.0)
N.B. If I run the select statement stating explicitly every column I wish to return (i.e. all of them, so there is no duplication of Unix_Time...then row 4 still returns but with row 1's Unix_Time in place of where it's own time should be. I.e. it returns:
(Both 1 & 4?) (u'8888', u'G', u'2014-12-11 19:19:19', u'At', u'2', u'10.168.77.10', u'A', 1407626637.0)
Lastly, if I run a similar statement requesting 'MAX' it appears to run fine.
Any help or words of wisdom is greatly appreciated.
