By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,756 Members | 1,745 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,756 IT Pros & Developers. It's quick & easy.

Displaying columns not in Group By or SELECT

P: 1
Hi All,

Basically how can I get around the GROUP BY rule of having column names in both SELECT and GROUP BY clauses?
I have a query that needs to display two key fields, a field that shows a MAX amount for each category, and also the 2 other supporting fields. My problems is getting the 3 supporting fields to show since GROUP BY only lets me display the aggregate field and the fields used to group. I think some type of JOIN might help but I can't get my head around it. Below is an example of my table.

Expand|Select|Wrap|Line Numbers
  1. Table:
  2. NAME     SESSION    SCORE      MINUTES          COLOR
  3. -----------    -------------     -----------       --------------          ------------
  4. Tom             1                30            10                       Red
  5. Bob              1               23              3                      Blue
  6. Bob              1               12             5                        Red
  7. Tom             2                 1             2                        Yellow 
  8. Bob             2                8              1                        Red
  9. Bob            2                16              3                         Red
  10.  
With Name & Session (together) being the key fields, SCORE being what I do MAX() on, and the other two as supporting data and I would like this output

Expand|Select|Wrap|Line Numbers
  1. NAME     SESSION    SCORE      TIME           COLOR
  2. -----------    -------------     -----------       -------          ------------
  3. TOM            1               30            10                  Red
  4. Tom             2              1                2               Yellow 
  5. Bob             1              23               3                  Blue
  6. Bob             2              16               3                   Red
  7.  
Thanks!
Dec 6 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.