Solved

detailed data from records returned from a GROUP BY query

Posted on 2008-10-02
6
175 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
  • 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

832 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