Results 1 to 15 of 15
  1. #1
    Join Date
    Apr 2004
    Location
    Australia
    Posts
    7

    Unanswered: Alternative to GROUP BY

    I have another question from my Uni assignment! I'm sure it's the way the questions are written that I'm having difficulty with!

    ==============================
    Write a query with the same meaning as the following query, but does not use a GROUP BY clause.

    SELECT EventId, MIN (ElapsedTime), MAX (ElapsedTime)
    FROM Results
    GROUP BY EventId
    =============================

    There are approx 8 results per event, each with an elapsed time - it is results of swimming races.

    I thought you had to use a GROUP BY with aggregate functions? I thought about temporary tables, but that doesn't eliminate the group by.

    Thanks!

    Nerddette

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what a wonderful question!!

    have you tried a three-way self join with two correlated subqueries?

    it will give the right answer, but it's not very efficient!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Tell your teacher he/she is an idiot and the query works perfectly well the way it's written. (grin)
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    derrick, it was an intellectual exercise

    whatsamatter, you couldn't do it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    No, I couldn't do it because it's stupid. I'm allergic to doing something stupid for intellectual reasons.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  6. #6
    Join Date
    Apr 2004
    Location
    Australia
    Posts
    7
    Quote Originally Posted by r937
    whatsamatter, you couldn't do it?
    * puts her hand up * Um... yes? I still can't do it!

    Any ideas, any commands that I can look up the syntax for? I think it might involve a subquery in the SELECT list, but I'm not sure.

    Thanks.

    Nerddette

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    nerddette, i gave you my idea in post #2

    i tested it and it works
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Without knowing which version of what database engine you are using, it is kind of tough to forumuate an answer. Assuming that your particular engine supports at least the basic SQL-92 syntax, you could do a SELECT DISTINCT to get the grouping done for you (it does the same thing, but doesn't require the GROUP BY clause). Once you've got that, you can probably use correlated sub-queries to get the Min and Max values for that particular EventId.

    If you are looking for "pre-cooked" SQL, ready to submit for a grade, you will probably wait a long time here. I'm always happy to help somebody out, but I'm allergic to doing other people's homework!

    I think that Rudy is just being perverse with the three-way self-join. I can see how it would work (although I wouldn't want to actually watch it), but I can think of at least 1e2 (an inside joke) easier ways to do it!

    -PatP

  9. #9
    Join Date
    Apr 2004
    Location
    Australia
    Posts
    7
    Quote Originally Posted by Pat Phelan
    If you are looking for "pre-cooked" SQL, ready to submit for a grade, you will probably wait a long time here. I'm always happy to help somebody out, but I'm allergic to doing other people's homework!
    Thanks for your help. I'm not looking for "pre-cooked" answers, but I understand your concerns as I get students at Uni wanting the same from me.

    Nerddette

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan
    I think that Rudy is just being perverse with the three-way self-join.
    nope

    it was the only way i could think of to do it

    i do not, however, see how your way would work, pat

    a correlated subquery can be used to restrict which value of ElapsedTime is chosen, but how do you get both min and max selected without a self-join?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jan 2004
    Posts
    51
    Folks,

    How about following

    select distinct EventId
    ,min_elapsed=(select min(b.ElapsedTime)
    from Results c
    where b.EventId=a.EventId)
    ,max_elapsed=(select max(c.ElapsedTime)
    from Results c
    where c.EventId=a.EventId)
    from Results a

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937
    a correlated subquery can be used to restrict which value of ElapsedTime is chosen, but how do you get both min and max selected without a self-join?
    Sushant is one character away from what I intended.

    -PatP

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sushant, that's eventually where my self-join was headed

    pat's right, i was being sneaky (i am trying to learn from a master)

    i wasn't just going to plop orthogonality into the discussion without some sort of leadup

    by the way you have a syntax error, or was that on purpose too? (just kidding)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937
    i wasn't just going to plop orthogonality into the discussion without some sort of leadup
    Awwww, why not ?!?!

    Sneaky? Is there somebody being sneaky around here? Who, where, how ??? Why am I always the last one to find out about these things ?!?!

    -PatP

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    speaking of orthogonality, i went in search of a few good links, and look what i found:

    RelationalWeenie

    two things of interest there: the entire wiki looks like a goldmine for computer related stuff, and look, it's another site with a two-character domain name -- do you have any idea what that domain name might be worth on the open market? and there i was, at the dawn of the web, when there were plenty of these names available, and i never bothered to snap a few up...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •