Solved

detailed data from records returned from a GROUP BY query

Posted on 2008-10-02
6
179 Views
Last Modified: 2012-05-05
Hi experts,

I need to get detailed record level data from records returned from a GROUP BY query.

In other words, get the minimum team_code values from each date, and then from those, get the opposition and kickoff values. I tried the attached code without success, but hopefully it will illustrate what I'm trying to do.

Any help much appreciated.  Oh, and this will be a MySQL query.

Cheers,  Col
SELECT fr_match_date, min(fr_team_code),
       ( SELECT fr_opposition, fr_kickoff
         FROM  fixtures_results XY
         WHERE XY.fr_match_date = AB.fr_match_date
         AND XY.fr_team_code = AB.fr_team_code
        ) 
 FROM fixtures_results AB
 WHERE fr_season_start = 2008
 AND fr_home_away = 'H'
 AND fr_team_code not in ('S', 'V', 'Y')
 GROUP BY fr_match_date
 ORDER BY fr_match_date, fr_team_code

Open in new window

0
Comment
Question by:colinspurs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 5

Accepted Solution

by:
jose_juan earned 500 total points
ID: 22623070
Hi,

umm.... I can't understand your data structure but....

direct method:

SELECT fr_match_date, min(fr_team_code),
       ( SELECT fr_opposition
         FROM  fixtures_results XY
         WHERE XY.fr_match_date = AB.fr_match_date
         AND XY.fr_team_code = AB.fr_team_code
        ) ,
       ( SELECT fr_kickoff
         FROM  fixtures_results XY
         WHERE XY.fr_match_date = AB.fr_match_date
         AND XY.fr_team_code = AB.fr_team_code
        )
FROM ...

with intermediate table

SELECT DISTINCT -- are needed I suppuse that you have many rows here
      fr_match_date, fr_team_code, fr_opposition, fr_kickoff
FROM fixtures_results

use this select into CROSS or insert on a variable table, ....

Good luck!
0
 
LVL 3

Author Comment

by:colinspurs
ID: 22623130
Jose Juan

Brilliant - that explains the error message - operand should contain 1 column.   The points are yours.

Would there be a different solution in Oracle SQL?  I used to use that and this style isn't familiar to me.

Cheers,  Col
0
 
LVL 9

Expert Comment

by:jamesgu
ID: 22623185
or you can use a derived table to get min team code, and then get the record from there

SELECT fr_opposition, fr_kickoff
         FROM  fixtures_results XY,
                                          (
                                          SELECT fr_match_date, min(fr_team_code),
                                           FROM fixtures_results AB
                                           WHERE fr_season_start = 2008
                                           AND fr_home_away = 'H'
                                           AND fr_team_code not in ('S', 'V', 'Y')
                                           GROUP BY fr_match_date
                                           ORDER BY fr_match_date, fr_team_code
                                          ) BB
     WHERE XY.fr_match_date = BB.fr_match_date
     AND XY.fr_team_code = BB.fr_team_code
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

Expert Comment

by:jamesgu
ID: 22623236
fixed typos, and this one works in oracle too

SELECT fr_opposition, fr_kickoff
         FROM  fixtures_results XY,
                                          (
                                          SELECT fr_match_date, min(fr_team_code) as min_fr_team_code
                                           FROM fixtures_results AB
                                           WHERE fr_season_start = 2008
                                           AND fr_home_away = 'H'
                                           AND fr_team_code not in ('S', 'V', 'Y')
                                           GROUP BY fr_match_date
                                           ORDER BY fr_match_date
                                          ) BB
     WHERE XY.fr_match_date = BB.fr_match_date
     AND XY.fr_team_code = BB.min_fr_team_code
     ;
0
 
LVL 3

Author Comment

by:colinspurs
ID: 22623342
jamesqu,

Thanks, yes that's more along the lines of what I had in mind.  Tahnks for your help, but Jose got there first.

Cheers,  Col
0
 
LVL 5

Expert Comment

by:jose_juan
ID: 22624405
Yes Colinspurs,

Jamesgu had put a correct solutions (if you delete the order by sentence), but is a concrete case of my general proposition

"use this select into CROSS or insert on a variable table, ...."

Thank's and Good luck!


0

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question