CODEWITHSUNDEEP
sqlpostgresqlsql-order-byaggregate-functions
Fong
Fong

Reputation: 115

Postgresql: STRING_AGG() optimal way to order by an undisplayed value

I am trying to generate a string of names in the order they logged in. I have working code but its ugly enough that I am sure I am missing a more direct method.

Goal, return a string of DISTINCT usernames in the order they last logged in.

  1. I start with a list of names and dates.
  2. I select the most recent row for each name. () t
  3. I order that by date. () s
  4. I STRING_AGG that giving me the string I want.

PgSql 9.6

Can I eliminate any of these nested queries?

SELECT
    STRING_AGG(s.name, ', ') AS names
FROM 
    (SELECT 
        t.name 
    FROM
        (SELECT DISTINCT ON (t2.name)
             t2.name
            ,t1.created
        FROM
            logins t1
        LEFT JOIN
            users t2 ON t2.id = t1.user_id
        ORDER BY t2.name, t1.created DESC
        ) t
    ORDER BY t.created DESC
    ) s

Upvotes: 2

Views: 905

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

Having you tried this?

SELECT STRING_AGG(s.name, ', ' ORDER BY created DESC) AS names
FROM (SELECT u.name, MAX(l.created) as created 
      FROM logins l JOIN
           users u
           ON u.id = l.user_id
      GROUP BY u.name
     ) ul;

The LEFT JOIN doesn't seem necessary. User ids in logins should be valid. If not, use the LEFT JOIN.

Upvotes: 2

Related Questions