Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

detailed data from records returned from a GROUP BY query

Posted on 2008-10-02
6
Medium Priority
?
183 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 2000 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
Monthly Recap

May was a big month for new releases from Linux Academy! Take a look at what our team built recently in our blog. You can access the newest releases from our blog.

 
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

Basic Security of Your VPC

So, you’ve got this shiny new VPC and a fancy new application configured on your EC2 servers ready to go. This application is only accessible from your computer, which is great for security, but you need your users to be able to access it! So, what’s the easiest way to do this?

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…
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. …
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

670 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