1

I have a special problem with my statistics query. I want to get the most recent username, duration, and connection count of every client that connected to a server. This is the query:

SELECT name, SUM(duration) AS time, COUNT(auth) AS connections
FROM analytics
WHERE duration IS NOT NULL
GROUP BY auth
ORDER BY time DESC;

The problem is that they query isn't showing the most recent username. It takes the first entrance of the client (identified by auth) in the database even if the client changed their username already.

Is there a way to get the most recent username of the client in the query above without slowing it down a lot?

Example Table:

| id | auth | name | duration |
|----|------|------|----------|
| 1  | u123 | Fire |    50    |
| 2  | u555 | Dan  |    20    |
| 3  | u123 | Ice  |    30    |

What I get:

| name |   time   | connections |
|------|----------|-------------|
| Fire |    80    |      2      |
| Dan  |    20    |      1      |

What I want

| name |   time   | connections |
|------|----------|-------------|
| Ice  |    80    |      2      |
| Dan  |    20    |      1      |
Ullas
  • 11,450
  • 4
  • 33
  • 50
Hannes
  • 78
  • 6
  • 1
    Supplying sample data and expected results would be helpful... – sgeddes Oct 03 '16 at 02:10
  • Do you have parameter that will tell you which username is the latest one ? I dont think it is possible without that. – happyHelper Oct 03 '16 at 02:11
  • Added the sample data and no I don't have a parameter. – Hannes Oct 03 '16 at 03:02
  • This [here](http://stackoverflow.com/questions/17318429/how-to-select-a-maximum-value-row-in-mysql-table) or [this other](http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) talk about similar situations. Reading them may help. –  Oct 03 '16 at 03:29

2 Answers2

1

I assume that you want most recent auth order by ID coz there is no other data to get it.

SELECT A.name, B.time, B.connections
FROM analytics AS A
INNER JOIN
(
SELECT auth, MAX(ID) AS MAXID, SUM(duration) AS time, COUNT(auth) as connections
FROM analytics B
WHERE duration IS NOT NULL
GROUP BY auth
) AS B
ON A.auth = B.auth AND A.ID = B.MAXID
Esty
  • 1,882
  • 3
  • 17
  • 36
0

If you have a TimeStamp column for the recent registred user. you can get it using the max()

select * from database where registered like (select max(registered) from database;

I wish it will help. Just analyze the idea.

Student
  • 3
  • 1
  • 5