This is the most complex query that I was struggling with... here is the solution

get_row_in_groupby_sql.md

This is the data...

Title	Author	Pages
A man from sky	Aayush	334
I know, what miracle is.	Aayush	3134
album001	Sameer	134
How to feel happiness?	Sameer	534
The secrets of woderland	Sameer	834
An Adventurous Guy	Baa	1234

Now I want the result like:

Author	Pages	Title
Aayush	3134	I know, what miracle is.
Sameer	834	The secrets of woderland
Baa	1234	An Adventurous Guy

Which is, group by authors, return the max pages by each and then also return the titles which have the max pages. I tried too much, but found this to be working...

SELECT L.author, L.pages, L.title
FROM books L
INNER JOIN
	(
	   SELECT author, MAX(pages) as pages
	   FROM BOOKS
	   GROUP BY author
	) R
ON L.author = R.author 
AND L.pages = R.pages

The inner query:

SELECT author, MAX(pages) as pages
FROM BOOKS
GROUP BY author

Returns:

Author	Max
Aayush	3134
Sameer	834
Baa	1234

So we inner join it with the real table on author and max page becomes true. The real hero is ON L.author = R.author AND L.pages = R.pages part.

Title	Author	Pages
A man from sky	Aayush	334
I know, what miracle is.	Aayush	3134
album001	Sameer	134
How to feel happiness?	Sameer	534
The secrets of woderland	Sameer	834
An Adventurous Guy	Baa	1234

Only those rows match and thus, return the appropriate information! Amazing!