CODEWITHSUNDEEP
mysqlsql
Daniel M
Daniel M

Reputation: 91

How to get the current id from an outer query in a nested select

I'm trying to fetch the most common value from a column when I group entries. I figured I have to use a GROUP BY in order to get that working. I have the following query:

SELECT post_id, (SELECT browser from visits WHERE
id = ? GROUP BY browser ORDER BY count(browser)
DESC limit 1) as common_browser, count(id) as visits FROM `visits` group by post_id

On the "?" I want to pass the ID of the group, it does what I'm trying to do if I pass a parameter to it, i.e: id = 1. If there's any other way to accomplish this, please let me know!

Upvotes: 1

Views: 1801

Answers (1)

Barmar
Barmar

Reputation: 782158

Use a correlated subquery.

SELECT post_id, (
    SELECT browser
    FROM visits AS v1
    WHERE v1.post_id = v.post_id
    GROUP by browser
    ORDER BY COUNT(*) DESC
    LIMIT 1) AS common_browser, COUNT(*) AS visits,
FROM visits AS v
GROUP BY post_id

Upvotes: 8

Related Questions