Get any value from a column aggregated by other column and where the max date is selected

Question

I'm an SQL novice and I have a table with the following columns

+-------+--------+----+----+
| email |  date  | IP | ID |
+-------+--------+----+----+

And I want to do something like this:

SELECT T.email,
       Max(T.date),
       T.ip     AS User_IP,
       T.id     AS ID

FROM LoginTable as T

WHERE (IP IS NOT NULL)

GROUP BY T.email

Of course this won't work because IP and ID are not in a (max) aggregate function. But I need that the IP matches the ID and the date, so I cannot use (max) in those columns because I would get results from different rows, and that's not an option.

To recap, I need:

A single row per each unique email (hence the group by)
The row selected is the one with the most recent date -> max(date)
I need IP and ID to be from the same row as the row selected by max(date).

@WhatsThePoint that would return more than a single row per email, because all ID's are unique and IP's can also be non-equal for a single user email. The reply from Mureinik and jophab works like a charm. Thank you! — RustyRocketeer, Nov 27 '17 at 10:23

score 4 · Answer 1 · answered Nov 27 '17 at 09:35

Window functions to the rescue:

SELECT email, date, user_ip, id
FROM   (SELECT email, date, ip AS user_ip, logid AS id, 
               ROW_NUMBER() OVER (PARTITION BY email ORDER BY date DESC) AS rn
        FROM   mytable
        WHERE  ip IS NOT NULL) t
WHERE  rn = 1

Note: This query will retrieve exactly one row per unique email. If you want to support records tied for the max date, you should use rank() instead of row_number().

jophab · Answer 2 · 2017-11-27T09:41:21.720

2

You can use Ranking Functions available in sql server instead of GROUP BY.

One example is ROW_NUMBER()

;WITH CTE AS (
 SELECT  ROW_NUMBER () OVER (PARTITION BY email ORDER BY DATE DESC) AS rn,
         email, 
         date,
         ip        AS User_IP,
         Logid     AS ID        
 FROM Table T WHERE (IP IS NOT NULL)
)
SELECT * FROM CTE WHERE RN=1

edited Nov 27 '17 at 09:41

answered Nov 27 '17 at 09:35

jophab

5,356
14
41
60

Sorry for the dumb question, but what does the first line in the query? ;WITH CTE AS ( – RustyRocketeer Nov 27 '17 at 10:30
It is a common table expression read more here https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql – jophab Nov 28 '17 at 05:29

Radim Bača · Answer 3 · 2017-11-27T09:47:16.047

1

This will work great if you have an index on Table(email, date)

SELECT email  AS email,
  date        AS Date,
  ip          AS User_IP,
  Logid       AS ID
FROM Table
WHERE IP IS NOT NULL and 
      (email, date) = (
           SELECT email, Max(date) 
           FROM Table 
           GROUP BY email
       )

edited Nov 27 '17 at 09:47

answered Nov 27 '17 at 09:40

Radim Bača

10,646
1
19
33

score 0 · Answer 4 · answered Nov 27 '17 at 09:35

Try this Answer,

SELECT L.email  AS email,
L.date      AS Date,
L.ip        AS User_IP,
L.Logid     AS ID
FROM Table AS T
WHERE EXISTS(
    SELECT email,MAX(Date)Date 
    FROM Table 
    GROUP BY email) AND (IP IS NOT NULL)
GROUP BY T.email

Hope this helps you.

Get any value from a column aggregated by other column and where the max date is selected

4 Answers4