• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 188
  • Last Modified:

detailed data from records returned from a GROUP BY query

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
colinspurs
Asked:
colinspurs
  • 2
  • 2
  • 2
1 Solution
 
jose_juanCommented:
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
 
colinspursAuthor Commented:
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
 
jamesguCommented:
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
jamesguCommented:
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
 
colinspursAuthor Commented:
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
 
jose_juanCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now