It feels like my SQL skills are pretty average these days, but that’s only because I haven’t had to do anything hard in a while. But just now I was happy to write this little SQL SELECT query that does a GROUP BY, an ORDER BY, and a COUNT, yielding the results shown in the image:
select nid, count(nid) as count_nid from term_node where tid in (3,1,11,10,9,8,7) group by nid order by count_nid DESC
I’m going to use this query — or one very similar to it — to get a list of nodes (nid) that have the most tag ids (tid) from the list of tid in the query. In theory, the nodes (blog posts) that have the most tags in common should be the most related to each other. So, in my Scrupal6 replacement for Drupal 6, this query is a way to get “related” content for a given blog post. (The tid list shown comes from node id 4, so I need to also exclude nid=4 from the results. I also need to add a limit clause to the query.)
If you ever need to do a group by, order by, and count in one SQL query, I hope this example is helpful.
