help with Query please

Hi,
I have a table with (sports) results, containing an userID, EventId and
a Time for each result recorded.
How do I select a list ordered from fastest to slowest containing the
fastest time for each userID recorded ? The challenge here, that I don't
understand how to do is to not get a list of all results for an event,
but only a single entry for each UserID with this userIDs fastest time.
Is there any way to do this except looping through each user ID from my
front end code and selecting the fastest time and build a dataset from
this that I order from a dataview ? Was hoping this could be done in SQL
rather than my VB .net code.
Any help appreciated.
Niclas
*** Sent via Developersdex http://www.examnotes.net ***It is hard to suggest something without seeing the code
SELECT * FROM Users WHERE datetime_column=
(SELECT MAX(datetime_column) FROM Users U WHERE U.userid=Users.userid)
"Niclas" <NOSpam@.Notmail.com> wrote in message
news:uEnR1rAdGHA.4900@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have a table with (sports) results, containing an userID, EventId and
> a Time for each result recorded.
> How do I select a list ordered from fastest to slowest containing the
> fastest time for each userID recorded ? The challenge here, that I don't
> understand how to do is to not get a list of all results for an event,
> but only a single entry for each UserID with this userIDs fastest time.
> Is there any way to do this except looping through each user ID from my
> front end code and selecting the fastest time and build a dataset from
> this that I order from a dataview ? Was hoping this could be done in SQL
> rather than my VB .net code.
> Any help appreciated.
> Niclas
>
> *** Sent via Developersdex http://www.examnotes.net ***|||If I understand your requirements correctly,
you can do this. Note that this will give you
all UserIDs that share the same fastest time
for an event.
SELECT s.UserID,
s.EventID,
s.RecordedTime
FROM SportsResults s
WHERE s.RecordedTime IN (SELECT MIN(s2.RecordedTime)
FROM SportsResults s2
WHERE s.EventID=s2.EventID)|||On Wed, 10 May 2006 01:23:02 -0700, Niclas wrote:

>Hi,
>I have a table with (sports) results, containing an userID, EventId and
>a Time for each result recorded.
>How do I select a list ordered from fastest to slowest containing the
>fastest time for each userID recorded ? The challenge here, that I don't
>understand how to do is to not get a list of all results for an event,
>but only a single entry for each UserID with this userIDs fastest time.
Hi Niclas,
SELECT userID, MIN([Time]) AS FastestTime
FROM YourTable
GROUP BY userID
ORDER BY FastestTime ASC
(Based on lots of assumptions - see www.aspfaq.com/5006 if I answered
the wrong question).
Hugo Kornelis, SQL Server MVP|||This is a fairly straight forward group by. See if this approach works for
you (If I understand your requirements correctly). Regardless of whether or
not this works, check out these links for a quick SQL overview. I think you
will find them helpful.
http://www.w3schools.com/sql/sql_intro.asp
http://sqlzoo.net/
Select userID
, EventId
min(Time) as BestTime
from MyTable
group by userID
, EventId
Order by BestTime
"Niclas" <NOSpam@.Notmail.com> wrote in message
news:uEnR1rAdGHA.4900@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have a table with (sports) results, containing an userID, EventId and
> a Time for each result recorded.
> How do I select a list ordered from fastest to slowest containing the
> fastest time for each userID recorded ? The challenge here, that I don't
> understand how to do is to not get a list of all results for an event,
> but only a single entry for each UserID with this userIDs fastest time.
> Is there any way to do this except looping through each user ID from my
> front end code and selecting the fastest time and build a dataset from
> this that I order from a dataview ? Was hoping this could be done in SQL
> rather than my VB .net code.
> Any help appreciated.
> Niclas
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Just what I needed, works OK.
Thanks
Niclas
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:uJZvYhDdGHA.4312@.TK2MSFTNGP05.phx.gbl...
> This is a fairly straight forward group by. See if this approach works
> for
> you (If I understand your requirements correctly). Regardless of whether
> or
> not this works, check out these links for a quick SQL overview. I think
> you
> will find them helpful.
> http://www.w3schools.com/sql/sql_intro.asp
> http://sqlzoo.net/
> Select userID
> , EventId
> min(Time) as BestTime
> from MyTable
> group by userID
> , EventId
> Order by BestTime
>
> "Niclas" <NOSpam@.Notmail.com> wrote in message
> news:uEnR1rAdGHA.4900@.TK2MSFTNGP02.phx.gbl...
>