Query help

25 Mar 2006 12:41 PM

Niclas

Hi,

I am struggling with a SQL query and was hoping to get some help.

This is a simplifed scenario:

I have 2 tables

Table1 Results with 2 columns USERID (int) and RESULT (datetime)

Table 2 Users UserID (int), firstname (varchar), lastname(varchar)

The results table stores datetime values for each event a userID have a
result recorded.

I would like a query that produces a list containing each USERID that is
present in the results table with the best (lowest) result.

In effect this would produce a personal best list for each user. Any
suggestions on how this can be achieved would be much appreciated.

Regards

Nic

25 Mar 2006 3:27 PM

Tom Moreau

See my previous response.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..

"Niclas" <lindblom_nic***@hotmail.com> wrote in message
news:uDWhnpAUGHA.1708@TK2MSFTNGP14.phx.gbl...
Hi,

I am struggling with a SQL query and was hoping to get some help.

This is a simplifed scenario:

I have 2 tables

Table1 Results with 2 columns USERID (int) and RESULT (datetime)

Table 2 Users UserID (int), firstname (varchar), lastname(varchar)

The results table stores datetime values for each event a userID have a
result recorded.

I would like a query that produces a list containing each USERID that is
present in the results table with the best (lowest) result.

In effect this would produce a personal best list for each user. Any
suggestions on how this can be achieved would be much appreciated.

Regards

Nic

26 Mar 2006 8:33 PM

Erland Sommarskog

[Reposted, as posts from outside msnews.microsoft.com does not seem to make
it in.]

Niclas (lindblom_nic***@hotmail.com) writes:
Show quote

> I am struggling with a SQL query and was hoping to get some help.
>
> This is a simplifed scenario:
>
> I have 2 tables
>
> Table1 Results with 2 columns USERID (int) and RESULT (datetime)
>
> Table 2 Users UserID (int), firstname (varchar), lastname(varchar)
>
> The results table stores datetime values for each event a userID have a
> result recorded.
>
> I would like a query that produces a list containing each USERID that is
> present in the results table with the best (lowest) result.
>
> In effect this would produce a personal best list for each user. Any
> suggestions on how this can be achieved would be much appreciated.

There were already two answers posted to your question, yet you repost it
for some reason.

Here is a slightly more elegant query than the ones posted by David and
Tom:

    SELECT u.firstname, u.lastname, r.result
    FROM   users u
    JOIN   (SELECT userid, result = MIN(result)
            FROM   results
            GROUP BY userid) AS r ON u.userid = r.userid

By using a derived table, I don't have to group on firstname and lastname.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.seBooks Online for SQL
Server 2005
athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000
athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx