MySql join two querys

Question

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

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 — zedfoxus
– zedfoxus, Commented Jun 22, 2014 at 4:07
I tried adding M.met_video_tipo DESC and it worked like a charm! Thanks! Add your comment as answer so I can upvote and check as the solution ;) — SnakeSheet
– SnakeSheet, Commented Jun 22, 2014 at 4:38

zedfoxus · Accepted Answer · 2014-06-22 04:48:29Z

1

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

answered Jun 22, 2014 at 4:48

zedfoxus

37.4k5 gold badges68 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Darshan Patel · Accepted Answer · 2014-06-22 04:26:45Z

1

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.

answered Jun 22, 2014 at 4:26

Darshan Patel

2,9272 gold badges27 silver badges39 bronze badges

Collectives™ on Stack Overflow

MySql join two querys

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related