CODEWITHSUNDEEP
mysqlsql
user13002303
user13002303

Reputation:

Converting a nested subquery into a join

Is it possible to convert this SQL into a JOIN?

  SELECT (SELECT t2.id
            FROM items t2
           WHERE t2.user_id = items.user_id
        ORDER BY [a list of cols that aren't stated here]
           LIMIT 1) AS id
    FROM items
   WHERE company_name = '....'
GROUP BY user_id

Upvotes: 1

Views: 41

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

Why both? Just use FIRST_VALUE():

SELECT DISTINCT col3,
       FIRST_VALUE(col4) OVER (PARTITION BY col3 ORDER BY col1)
FROM tbl
WHERE col2;

Upvotes: 1

Related Questions