0

I checked the following questions:

GROUP BY with MAX(DATE)

Fetch the row which has the Max value for a column

Columns : account_number, app_guid, time_event_published(epoch time).

I want the latest row of each app_guid for a given account_number PLUS oldest time_event_published of the same account_number for each app_guid in another column of the latest row.

SELECT id, account_number, app_guid, time_event_published , <oldest_time_event_published_for_2152331553409959696> FROM ( 
  SELECT id, account_number, app_guid, time_event_published,
    RANK() OVER (PARTITION BY app_guid ORDER BY time_event_published DESC) dest_rank
    FROM event where account_number=2152331553409959696
  ) where dest_rank = 1;

I am only able to think of another DB hit with same query with ASC. Is there any other way and how to approach this requirement? 

DB Entries:
2152331553409959696, TEST-ONE-APP_GUID, 25-JAN
2152331553409959696, TEST-ONE-APP_GUID, 1-JAN

2152331553409959696, TEST-TWO-APP_GUID, 25-FEB
2152331553409959696, TEST-TWO-APP_GUID, 1-FEB

Required Result:
2152331553409959696, TEST-ONE-APP_GUID, 25-JAN, 1-JAN
2152331553409959696, TEST-TWO-APP_GUID, 25-FEB, 1-FEB
user104309
  • 690
  • 9
  • 20
  • Just trying to understand the question, do you want the oldest time_event_published associated with any record for the given account number, or the oldest associated with the latest app_guid for the given account number? – Error_2646 Sep 05 '17 at 20:39
  • 'oldest associated with the `latest` app_guid' does not make sense. What I need is oldest associated for 'each' appguid for the given account as another column beside the latest row. – user104309 Sep 05 '17 at 20:58
  • Sorry about that, I think I understand what you mean now. – Error_2646 Sep 05 '17 at 20:59

2 Answers2

1

If I understood your question correctly then I think the below SQL will do what you require:

SELECT id, account_number, app_guid, time_event_published , oldest_time_event_published
FROM ( 
  SELECT id, account_number, app_guid, time_event_published,
    RANK() OVER (PARTITION BY app_guid ORDER BY time_event_published DESC) dest_rank,
    MIN (time_event_published) OVER (PARTITION BY app_guid) oldest_time_event_published
        FROM event where account_number=2152331553409959696
  ) where dest_rank = 1;

Let me know how it works with your data and let us know.

I haven't tested with sample data but I am pretty confident it will work for you!

Ted.

pricco
  • 2,763
  • 1
  • 21
  • 22
Ted at ORCL.Pro
  • 1,602
  • 1
  • 7
  • 10
  • This is definitely better if only one id can be associated with the account. My answer had to do some gymnastics assuming that the output should be at the distinct {app_guid, latest time_event_published} level. – Error_2646 Sep 05 '17 at 23:01
  • I would be happy to assist further, but you need to explain to me in more detail where to go from here. Also, more sample data will be of help :) – Ted at ORCL.Pro Sep 06 '17 at 06:14
  • @TedFilippidis Looks like this is what I want. I will experiment more and come back. – user104309 Sep 06 '17 at 16:10
  • OK Great, dont forget to mark as answered when satisfied with the results. – Ted at ORCL.Pro Sep 06 '17 at 19:04
  • Is any option similar to the following available ? `MIN (time_event_published) OVER (PARTITION BY app_guid where event_type='L') oldest_login`. `WHERE` condition nearby `RANK()` or `MIN()` – user104309 Oct 16 '17 at 14:19
0

This should work, it's not very attractive though, whether it's better than hitting the table twice depends on performance and readability. 

SELECT  MAX(CASE WHEN dest_rank = 1 THEN id
                 ELSE NULL
             END
           ) AS id,
        MAX(CASE WHEN dest_rank = 1 THEN account_number
                 ELSE NULL
             END
           ) AS account_number,
        app_guid,
        MAX(CASE WHEN dest_rank = 1 THEN time_event_published
                 ELSE NULL
             END
           ) AS time_event_published,
        MAX(CASE WHEN dest_rank_asc = 1 THEN time_event_published
                 ELSE NULL
             END
           ) AS earliest_time_event_published
  FROM (SELECT id, 
               account_number, 
               app_guid, 
               time_event_published,
               RANK() OVER 
                 ( PARTITION BY app_guid 
                        ORDER BY time_event_published DESC
                 ) dest_rank,
               RANK() OVER 
                 ( PARTITION BY app_guid 
                       ORDER BY time_event_published ASC
                 ) dest_rank_asc,
          FROM event 
         WHERE account_number=2152331553409959696
        ) 
 GROUP
    BY app_guid;
Error_2646
  • 2,555
  • 1
  • 10
  • 22