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

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!!

Tell your teacher he/she is an idiot and the query works perfectly well the way it's written. (grin)

derrick, it was an intellectual exercise

whatsamatter, you couldn't do it?

No, I couldn't do it because it's stupid. I'm allergic to doing something stupid for intellectual reasons.

* 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

nerddette, i gave you my idea in post #2

i tested it and it works

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

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

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?

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

Sushant is one character away from what I intended.

-PatP

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)

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

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...