I have a table with 5 columns:
- tag 1
- tag 2
- tag 3
- tag 4
- tag 5
If I want to show results ordered by the popularity(frequency) of those tags, what kind of query would i use?
+3
A:
Because the table isn't normalized, you'll have to flatten it first:
SELECT a.column, a.tag1 AS tag
FROM TABLE a
UNION ALL
SELECT b.column, b.tag2
FROM TABLE b
UNION ALL
SELECT c.column, c.tag3
FROM TABLE c
UNION ALL
SELECT d.column, d.tag4
FROM TABLE d
UNION ALL
SELECT e.column, e.tag5
FROM TABLE e
...before you can count them:
SELECT t.tag, COUNT(*) tag_popularity
FROM (SELECT a.column, a.tag1 AS tag
FROM TABLE a
UNION ALL
SELECT b.column, b.tag2
FROM TABLE b
UNION ALL
SELECT c.column, c.tag3
FROM TABLE c
UNION ALL
SELECT d.column, d.tag4
FROM TABLE d
UNION ALL
SELECT e.column, e.tag5
FROM TABLE e) x
GROUP BY x.tag
ORDER BY tag_popularity DESC
OMG Ponies
2010-08-12 00:36:03
wow... i didn't think it would be so complicated. glad i asked!
doug
2010-08-12 01:26:59
does union actually change anything in my database?
doug
2010-08-12 01:29:48
mmm there is no TABLE a TABLE b TABLE c TABLE d TABLE e, or am i failing to understand whats going on here
doug
2010-08-12 01:34:37
@doug: Only statement starting with INSERT, UPDATE, or DELETE change data
OMG Ponies
2010-08-12 01:44:42
@doug: TABLE a/b/c/d/e is the same table, with a different table alias so it's clear which instance is being referenced. It'll allow you to update the "TABLE" instances in the statement without affecting the query.
OMG Ponies
2010-08-12 01:45:51
wow thanks heaps. ill keep you posted on how it goes, but i certainly think you have nailed it. I have never to use groups or unions so im keen to get this under my belt
doug
2010-08-12 02:16:58