CODEWITHSUNDEEP
mysql
rahul
rahul

Reputation: 418

How to select the max table from join table?

I have table name posts and another table which name is counttbl

In my post table there is column name

postid(primarykey), postdetails, postdate(timestamp)

And in counttbl there are 3 column which are

id(primarykey), postid,countnumber

I want to select that post from counttbl which has maximumnumber of count,

E.g. in post table I have

postid = 1, postdetails = details1, date = 29:11:00 00:00:00

And in count there is postid = 1, countnumber = 4, and postid = 2, countnumber = 3

Then I want to select that post which has maximumber count number and show that post details using join.

Upvotes: 0

Views: 114

Answers (5)

Golwin
Golwin

Reputation: 157

try this

select * from posts  WHERE post_id IN (select MAX(countnumber) from counttble )

Upvotes: 0

Clemjon Gubaton
Clemjon Gubaton

Reputation: 1

try this:

select * from post where postid in (select postid from counttbl having max(countnumber));

Upvotes: 0

halliballi
halliballi

Reputation: 130

This statement would give you the desired result. I named the tables and fields differen because of possible conflicts with reserved words in SQL.

SELECT * FROM 
(
  SELECT counts.postid, counts.counts 
  FROM counts 
  WHERE counts.counts = (SELECT max(counts) FROM counts) 
) tempcounts 
INNER JOIN posts ON posts.postid = tempcounts.postid 
ORDER BY posts.postdate DESC limit 0,1

If more post have the same count they all will be in the result

Upvotes: 1

Raghbendra Nayak
Raghbendra Nayak

Reputation: 1646

Please use below query:

SELECT MAX(cnt.countnumber), cnt.postid
FROM counttbl as cnt
JOIN post as pst ON cnt.postid = pst.id

I have given table name:

Table name: post and counttbl

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

You can try below

select * from posts p 
inner join 
(
  select postid, max(countnumber) from counttbl group by postid
) as p1
on p.postid=p1.postid

Upvotes: 0

Related Questions