database - How to get and sort data from this MYSQL query -

update: question updated people undestand better

i have following tables

create table if not exists `post` (   `id` int(11) not null auto_increment,   `heading` text not null,   `content` longtext not null,   `date` timestamp not null default current_timestamp,   `thumb` text null,   primary key (`id`)) engine = innodb auto_increment = 15 default character set = latin1;  create table if not exists `tags` (   `id` int(11) not null auto_increment,   `tagname` text not null,   primary key (`id`)) engine = innodb auto_increment = 12 default character set = latin1;  drop table if exists `tagsinpost` ;  create table if not exists `tagsinpost` (   `tid` int(11) null default null,   `pid` int(11) null default null,   constraint `post_ibfk_1`     foreign key (`pid`)     references `post` (`id`)     on delete cascade     on update cascade,   constraint `tagsinpost_ibfk_1`     foreign key (`tid`)     references `tags` (`id`)     on delete cascade     on update cascade) engine = innodb default character set = latin1;  create index `tagsinpost_ibfk_1` on `tagsinpost` (`tid` asc);  create index `post_ibfk_1` on ``tagsinpost` (`pid` asc); 

where tid , pid foreign keys ofcourse tag id , post id

now want related post based on tags of post. example if post1 contains tag t1, t2, t3, t4 , post2 contains tag t2, t3, t4 , post3 contains t1, t2 , searching posts related post1, output should be,

post2 post3

notice order in because of number of tags matched in them. stuck @ finding posts having same tags without order , query taking tags input should post id.

select distinct p.id, p.heading, p.date  post p, tags t, tagsinpost tp  tp.tid=t.id    , tp.pid=p.id    , t.tagname in (                    select distinct t.tagname tags t, tagsinpost                     tp tp.pid = 20 , tp.tid = t.id                    ) 

now how order output number of matches t.tagname found in second query?

how order output number of matches

you need join , use of group achieve this. e.g

select t1.col1, count(*) count_of table1 t1 left join table2 t2 on t1.id = t2.fk group t1.col1 order count_of desc 

here can count rows , order calculation.