Jump to content

Archived

This topic is now archived and is closed to further replies.

linkin

this is quite a challenge but any help is appreciated

Recommended Posts

ok basicly I have a table, which contain three columns:

ID | ID2 | ID3
--------------
1    5        6
2    3        8
3    3        7
4    3        9
5    25      18
--------------

the inputs will be ID3 (e.g. 7,9,18) i.e multiple inputs, not just one
the output needs to be: which is the most popular ID2, based on how many ID3 shared. sorted by popularity.
e.g. input of 7,9,8,18 will result in 3 and then 25

all i need is the SQL query, if you can help i would appreciate it
if you need more explanation, tell me.
thanks in advance

Share this post


Link to post
Share on other sites
If I understand you correctly, then a query like this will get you the result you want:

SELECT
  id2
, COUNT(id2) AS id2_count
FROM
ids_table
WHERE
id3 IN (7, 9, 8, 18)
GROUP BY
id2
ORDER BY
id2_count DESC  # most popular first
;

Outputs something like:

id2  id2_count
---  ----------
3          3
25        1


When you want to only select the most popular that have at least 2 or more entries, then use the HAVING clause, as in this example:

SELECT
  id2
, COUNT(id2) AS id2_count
FROM
ids_table
WHERE
id3 IN (7, 9, 8, 18)
GROUP BY
id2
HAVING
id2_count > 1    # At least 2 or more hits
ORDER BY
id2_count DESC  # most popular first
;

Which would only return:

id2  id2_count
---  ----------
3          3

Notice it would not return the 25 in this example.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.