Solved

detailed data from records returned from a GROUP BY query

Posted on 2008-10-02
6
174 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

'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 …
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…

929 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now