Reputation: 151
With this query:
SELECT entry.ent_video_tipo, count(ent_video_tipo) as cnt
FROM entry
WHERE entry.ent_user='1'
GROUP BY ent_video_tipo
ORDER BY cnt DESC
I get:
ent_video_tipo|cnt
1 | 3
3 | 2
4 | 1
2 | 1
I got this other query:
SELECT * FROM meta
And I get:
met_id|met_name|met_video_tipo
1 | bla | 4
8 | bla | 4
10 | bla | 2
11 | bla | 3
14 | bla | 1
21 | bla | 1
22 | bla | 1
41 | bla | 3
When I try this query
SELECT M.*
FROM meta M,
INNER JOIN
(SELECT entry.ent_video_tipo, count(ent_video_tipo) as cnt
FROM entry
WHERE entry.ent_user='1'
GROUP BY ent_video_tipo
ORDER BY cnt DESC) E
ON M.met_video_tipo = E.ent_video_tipo
ORDER BY E.cnt DESC
I get this:
met_id|met_name|met_video_tipo
21 | bla | 1
14 | bla | 1
22 | bla | 1
41 | bla | 3
11 | bla | 3
10 | bla | 2
8 | bla | 4
1 | bla | 4
But this is almost what it should return. The only thing wrong is that the column met_video_tipo
should be 1,3,4,2
and not 1,3,2,4
.
Is there anyway to return like this ? ?
met_id|met_name|met_video_tipo
21 | bla | 1
14 | bla | 1
22 | bla | 1
41 | bla | 3
11 | bla | 3
8 | bla | 4
1 | bla | 4
10 | bla | 2
Upvotes: 0
Views: 57
Reputation: 37029
The database is sorting correctly based on what you asked, which is E.CNT DESC.
met_video_tipo 4 and 2 have the same count of 1. Since order by was not defined for met_video_tipo, the database has the liberty to change the order of met_video_tipo based on various conditions.
To explicitly order it like you want, try ORDER by e.cnt DESC, m.met_video_tipo DESC
Upvotes: 1
Reputation: 2899
Maybe you don't need to create another table. Try this :
select a.met_id,a.met_name,a.met_video_tipo from meta a,
(select met_video_tipo,count(*) as cnt from meta group by met_video_tipo) as x
where x.met_video_tipo=a.met_video_tipo order by x.cnt desc;
You can use entry table as well instead of group by query.
Upvotes: 1