CODEWITHSUNDEEP
phpsql
masterCoder
masterCoder

Reputation: 5

Sorting multiple values based on how frequent they appear

I am retrieving data from a database using php / sql. The sql code that retrieves data is

SELECT user_id
FROM table
WHERE (field_id = 16 AND value BETWEEN 1 AND 100)
OR (filed_id = 17 AND value BETWEEN 1 AND 100)

(the or's would be multiple or 1, or 0)

And it would return an array of values similar to

$arry = {4,1,5,4,1,5}

something like that. If I change the query to something like

SELECT user_id
FROM table
WHERE (field_id = 16 AND value BETWEEN 1 AND 100)
OR (filed_id = 17 AND value BETWEEN 1 AND 100)
GROUP BY user_id

It would return something like

$arry = {1,4,5}

How can I organize the results so that the most frequent user_id which appears comes first in descending order?

SELECT count(user_id) AS cnt, user_id
FROM table
WHERE (field_id = 16 AND value BETWEEN 1 AND 100)
OR (filed_id = 17 AND value BETWEEN 1 AND 100)
ORDER BY cnt

I tried a query similar to that, but it screws up all the results.

Upvotes: 0

Views: 49

Answers (1)

Mihai
Mihai

Reputation: 26784

SELECT user_id,COUNT(*) 
FROM table
WHERE (field_id = 16 AND value BETWEEN 1 AND 100) 
OR (filed_id = 17 AND value BETWEEN 1 AND 100) 
GROUP BY user_id
ORDER BY COUNT(*) DESC

Upvotes: 2

Related Questions