SnakeSheet
SnakeSheet

Reputation: 151

MySql join two querys

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

Answers (2)

zedfoxus
zedfoxus

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

Darshan Patel
Darshan Patel

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

Related Questions