CODEWITHSUNDEEP
mysqlsql
Raymond C Borges Hink
Raymond C Borges Hink

Reputation: 420

Selecting the rows within a MySQL database with max values on a single column

I have a MySQL table named NetLogs

servername   , session , label     , occurence
| Nost       |   11973 | Unknown   |   1 |
| Nost       |   11976 | Scan      |  74 |
| Nost       |   11976 | Unknown   |  35 |
| Gold       |   11998 | Attack    |   1 |

I need to get

Nost | 11973|unknown|1|
Nost| 11976 |Scan | 74|
Gold|11998|Attack|1|

as a result.

I tried:

select t1.* from NetLogs t1 left join NetLogs t2 
on t1.servername=t2.servername and t1.session=t2.session and t1.occurence < t2.occurence 
where t2.occurence is null;

but I get Error 1137 - Can't reopen table.

I would also settle for the same result without the number of occurrence at the end.

So some of the solutions below didn't work for me so I remade the table as a regular table and not a temporary table and they worked, which makes me think I may have had a working query, at one point, but was getting the error because I was running it on a temporary table...

This is what works exactly how I wanted it:

select a.* from NetLogs a where a.occurence = ( Select max(occurence) occurence from NetLogs b where a.session = b.session and a.serverName = b.serverName);

Upvotes: 1

Views: 76

Answers (2)

Justin
Justin

Reputation: 9724

Query:

SQLFIDDLEEXample

SELECT  a.servername,
        a.session,
        a.label,
        a.occurence
FROM    NetLogs a
 LEFT JOIN NetLogs b
  ON b.session = a.session
  AND a.occurence < b.occurence
WHERE b.session IS NULL

Result:

| SERVERNAME | SESSION |   LABEL | OCCURENCE |
|------------|---------|---------|-----------|
|       Nost |   11973 | Unknown |         1 |
|       Nost |   11976 |    Scan |        74 |
|       Gold |   11998 |  Attack |         1 |

Upvotes: 0

John Woo
John Woo

Reputation: 263693

SELECT  a.*
FROM    NetLogs a
        INNER JOIN
        (
            SELECT  session, MAX(occurence) occurence
            FROM    NetLogs
            GROUP   BY session
        ) b ON a.session = b.session AND
                a.occurence = b.occurence

Another way,

SELECT  a.*
FROM    NetLogs a
WHERE   a.occurence = 
        (
            SELECT  MAX(occurence) occurence
            FROM    NetLogs b
            WHERE   a.session = b.session
        )

Upvotes: 2

Related Questions