Help with max query 1

[sonname]

I have two tables. Books and bookPages. What would be the query that would hand me
back the pageId of all pages that are the last page in the book. Basically I would want all pageId's
in all distinct books that claim to be the last page of the book


Book BookPages

bookName bookId
bookId pageNumber
pageId

Here is some example data for the Book table

bookName bookId
'myBook1' 1
'myBook2' 2
'myBook3' 3


Here is some example data for the Book Pages table

bookId pageNumber pageId
1 1 20
1 2 21
1 3 23
2 1 24
2 2 25
2 3 26
2 4 27


Here are the results that I want
bookId pageNumber pageId
1 3 23
2 4 27

 

[sunila7]

Hi,

Try this

Select bookid,max(pagenumber),max(pageid) From [Book Pages]
Group by Bookid


Sunil

 

[camelman]

Did that work ? I am trying to do the same thing and I got the highest pagenumber and the highest page ID even though they were from different records. What I am trying to get is the highest pagenumber and then the corresponding pageid for that record.

Camelman

 

[sonname]

This is exactly the problem. What I want is the highest page number with its corresponding pageId. In the previous query, there shouldn't be a max on the pageId. When I take the max out from the pageid, I get too many rows returned, basically I get the same bookId showing multiple times.

 

[camelman]

Then you and I have the same problem. I have posted a question called "more problems with max querys"
it is driving me nuts.

I have a solution for you but it is really slow if you have alot of records. I am using it until I find a decent solution.

Create VIEW1:
Select bookid,max(pagenumber) as lastpage From [Book Pages]
Group by Bookid

Now create another view (or sp) which joins VIEW1 to the [book pages] table. Join by bookID AND lastpage.
This will give you the pageID of each of these records.

If you find a real solution please post it because I have been trying to work this out for ages.

 

[sunila7]

Hi,

Try this query....

Select * from [Book Pages] B
Inner Join
(Select bookid,max(pagenumber) pn
From [Book Pages]
Group by Bookid) TBL ON B.BookId=TBL.BookID and Tbl.pn=B.Pagenumber


Sunil

 

[sonname]

Works like a charm! Thanks