Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2007
    Posts
    20

    Unanswered: How would I do this - select with aggregate function

    I have a table like this below:
    HTML Code:
    <PRE>
    Page       Book      Release                MaxPages
    1234	ABC        A		1	
    1234	ABC        B		2	
    9999	ABC        D		1	
    9999	ABC        E		2
    7777	ABC        A		1	
    7777	ABC        C		2
    </PRE>
    I want to select every page of the book, but only the highest release of that page.

    Something Like:
    Select Book, Page, Max(Release), MaxPages
    From Table
    But I can't quiet figure it out.

    Thanks for any help.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Group by page, max(release) in a derived table. Join this to your table on those two columns.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2007
    Posts
    20
    I'm sorry. My bad.

    The table looks more like this:
    HTML Code:
    <PRE>
    Page       Book      Release                MaxPages
    1234	BOOK1        A		1	
    1234	BOOK1        B		2	
    9999	BOOK2        D		1	
    9999	BOOK2        E		2
    7777	BOOK3        A		1	
    7777	BOOK3        C		2
    </PRE>
    I'm not sure your answer applies now. I don't think it does. I need to get the latest relase of each page from each Book.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Exactly the same principle. You are just grouping by one more column
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2007
    Posts
    20
    This syntax look correct?

    Code:
    Select * From
    (
      Select Book, Page, Max(Release), MaxPages
      From Table
      Group By Book, Page, Max(Release)
    ) as temp(Book, Page, Release, MaxPages)
    Join Table  on (Table.Book = temp.Book)

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Nope - but you ran it right? What did it say?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2007
    Posts
    20
    My actual query is much bigger, with different table names etc...

    I think it would be more like this (after running it)

    Code:
    Select Table.Book, Table.Page, temp.Release
     From
    (
      Select Book, Page, Max(Release) as Release, MaxPages
      From Table
      Group By Book, Page, MaxPages
    ) as temp(Book, Page, Release, MaxPages)
    Join Table  on (Table.Book = temp.Book AND Table.Page=temp.Page AND Table.Release=temp.Release)
    Am I getting closer? (Still can't get it to run. Just says error in FROM clause)

    And in the GROUP clause, I can't use the aggregate function and all other fields that I selected must be listed there. Correct?

  8. #8
    Join Date
    Feb 2007
    Posts
    20
    I figured it out.
    It wasn't allowing me to use Join On syntax, I had to use WHERE tbl.yadda=temp.yadda to join the tables.

    btw,I'm using a .net dataadpter so that may have been contributing to my error. Don't know...

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    specifying derived table column names in a list appears not to work in sql server, even though it is standard sql

    so instead of
    Code:
    SELECT ... 
      FROM ( SELECT Book
                  , Page
                  , Max(Release) as Release
                  , MaxPages
               FROM Table
             GROUP 
                 BY Book
                  , Page
                  , MaxPages
           ) AS temp 
                  ( Book
                  , Page
                  , Release
                  , MaxPages )
    INNER
      JOIN ...
    you have to assign all column names in the subquery, which you already did --
    Code:
    SELECT ... 
      FROM ( SELECT Book
                  , Page
                  , Max(Release) as Release
                  , MaxPages
               FROM Table
             GROUP 
                 BY Book
                  , Page
                  , MaxPages
           ) AS temp
    INNER
      JOIN ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by landrun
    I figured it out.
    It wasn't allowing me to use Join On syntax,
    no, that wasn't it, you can use JOIN syntax
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Feb 2007
    Posts
    20
    Quote Originally Posted by r937
    no, that wasn't it, you can use JOIN syntax
    I think its the .net Adapter that wasn't letting me do it.
    Oh... or maybe I'm posting in the wrong forum. I'm querying an Access database

    At any rate, once I changed the way I joined the tables from JOIN ON to WHERE a.a=b.a etc... the adapter stopped returning errors and returned a recordset for me.

    I really do appreciate the help. And you did help me.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002




    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •