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!