Prev: FakeWorkTable error
Next: COUNT and AVG problem
From: Nic on 22 Jun 2007 04:25
Hi,

I would appreciate some help with a SQL Query - Sample data provided below.

I have a table with results and I would like to create a query that returns
the lowest (fastest) datetime value for each eventID that exists for a
specific Athleteid passed in as a parameter

In the sample below, I would expect a result set consisting of two rows with
the fastest value for eventid 1 and eventid 2 where the AtheleteID is in the
WHERE clause

Let me know if further clarification is required. Any help would be much
appreciated.

Nic


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [IndResults](
[ResultID] [int] IDENTITY(1,1) NOT NULL,
[AthleteID] [int] NOT NULL,
[EventID] [int] NOT NULL,
[MeetingID] [int] NOT NULL,
[ResultTypeID] [int] NOT NULL,
[Result] [datetime] NOT NULL,
[ResultDate] [datetime] NOT NULL,
CONSTRAINT [PK_Results] PRIMARY KEY CLUSTERED
(
[ResultID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
INSERT Indresults
(AthleteID,EventID,MeetingID,ResultTypeID,Result,ResultDate) VALUES
(1,1,1,1,'01/01/1900 00:00:35.750','01/01/2005')
INSERT Indresults
(AthleteID,EventID,MeetingID,ResultTypeID,Result,ResultDate) VALUES
(1,1,1,1,'01/01/1900 00:00:36.750','01/01/2005')
INSERT Indresults
(AthleteID,EventID,MeetingID,ResultTypeID,Result,ResultDate) VALUES
(1,1,1,1,'01/01/1900 00:00:37.750','01/01/2005')
INSERT Indresults
(AthleteID,EventID,MeetingID,ResultTypeID,Result,ResultDate) VALUES
(1,2,1,1,'01/01/1900 00:01:35.750','01/01/2005')
INSERT Indresults
(AthleteID,EventID,MeetingID,ResultTypeID,Result,ResultDate) VALUES
(1,2,1,1,'01/01/1900 00:01:36.750','01/01/2005')
INSERT Indresults
(AthleteID,EventID,MeetingID,ResultTypeID,Result,ResultDate) VALUES
(1,2,1,1,'01/01/1900 00:01:37.750','01/01/2005')

From: markc600 on 22 Jun 2007 04:55

DECLARE @AthleteID int

SET @AthleteID=1;

WITH CTE AS(
SELECT AthleteID,
EventID,
MeetingID,
ResultTypeID,
Result,
ResultDate,
ROW_NUMBER() OVER(PARTITION BY EventID ORDER BY Result) as rn
FROM Indresults
WHERE AthleteID=(a)AthleteID)
SELECT AthleteID,
EventID,
MeetingID,
ResultTypeID,
Result,
ResultDate
FROM CTE
WHERE rn=1

From: xyb on 22 Jun 2007 04:58
On 6 22 , 4 25 , "Nic" <NOlindblom_nic...(a)hotmail.comSPAM> wrote:
> Hi,
>
> I would appreciate some help with a SQL Query - Sample data provided below.
>
> I have a table with results and I would like to create a query that returns
> the lowest (fastest) datetime value for each eventID that exists for a
> specific Athleteid passed in as a parameter
>
> In the sample below, I would expect a result set consisting of two rows with
> the fastest value for eventid 1 and eventid 2 where the AtheleteID is in the
> WHERE clause
>
> Let me know if further clarification is required. Any help would be much
> appreciated.
>
> Nic
>
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE TABLE [IndResults](
> [ResultID] [int] IDENTITY(1,1) NOT NULL,
> [AthleteID] [int] NOT NULL,
> [EventID] [int] NOT NULL,
> [MeetingID] [int] NOT NULL,
> [ResultTypeID] [int] NOT NULL,
> [Result] [datetime] NOT NULL,
> [ResultDate] [datetime] NOT NULL,
> CONSTRAINT [PK_Results] PRIMARY KEY CLUSTERED
> (
> [ResultID] ASC
> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
> OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> ) ON [PRIMARY]
>
> GO
> INSERT Indresults
> (AthleteID,EventID,MeetingID,ResultTypeID,Result,ResultDate) VALUES
> (1,1,1,1,'01/01/1900 00:00:35.750','01/01/2005')
> INSERT Indresults
> (AthleteID,EventID,MeetingID,ResultTypeID,Result,ResultDate) VALUES
> (1,1,1,1,'01/01/1900 00:00:36.750','01/01/2005')
> INSERT Indresults
> (AthleteID,EventID,MeetingID,ResultTypeID,Result,ResultDate) VALUES
> (1,1,1,1,'01/01/1900 00:00:37.750','01/01/2005')
> INSERT Indresults
> (AthleteID,EventID,MeetingID,ResultTypeID,Result,ResultDate) VALUES
> (1,2,1,1,'01/01/1900 00:01:35.750','01/01/2005')
> INSERT Indresults
> (AthleteID,EventID,MeetingID,ResultTypeID,Result,ResultDate) VALUES
> (1,2,1,1,'01/01/1900 00:01:36.750','01/01/2005')
> INSERT Indresults
> (AthleteID,EventID,MeetingID,ResultTypeID,Result,ResultDate) VALUES
> (1,2,1,1,'01/01/1900 00:01:37.750','01/01/2005')

Try:
select
EventID,min(Result)
from Indresults
group by EventID

From: Nic on 23 Jun 2007 15:44

"xyb" <xiangyuanbo(a)gmail.com> wrote in message
news:1182502684.550214.77570(a)a26g2000pre.googlegroups.com...
> On 6 22 , 4 25 , "Nic" <NOlindblom_nic...(a)hotmail.comSPAM> wrote:
>> Hi,
>>
>> I would appreciate some help with a SQL Query - Sample data provided
>> below.
>>
>> I have a table with results and I would like to create a query that
>> returns
>> the lowest (fastest) datetime value for each eventID that exists for a
>> specific Athleteid passed in as a parameter
>>
>> In the sample below, I would expect a result set consisting of two rows
>> with
>> the fastest value for eventid 1 and eventid 2 where the AtheleteID is in
>> the
>> WHERE clause
>>
>> Let me know if further clarification is required. Any help would be much
>> appreciated.
>>
>> Nic
>>
>> SET ANSI_NULLS ON
>> GO
>> SET QUOTED_IDENTIFIER ON
>> GO
>> CREATE TABLE [IndResults](
>> [ResultID] [int] IDENTITY(1,1) NOT NULL,
>> [AthleteID] [int] NOT NULL,
>> [EventID] [int] NOT NULL,
>> [MeetingID] [int] NOT NULL,
>> [ResultTypeID] [int] NOT NULL,
>> [Result] [datetime] NOT NULL,
>> [ResultDate] [datetime] NOT NULL,
>> CONSTRAINT [PK_Results] PRIMARY KEY CLUSTERED
>> (
>> [ResultID] ASC
>> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
>> OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
>> ) ON [PRIMARY]
>>
>> GO
>> INSERT Indresults
>> (AthleteID,EventID,MeetingID,ResultTypeID,Result,ResultDate) VALUES
>> (1,1,1,1,'01/01/1900 00:00:35.750','01/01/2005')
>> INSERT Indresults
>> (AthleteID,EventID,MeetingID,ResultTypeID,Result,ResultDate) VALUES
>> (1,1,1,1,'01/01/1900 00:00:36.750','01/01/2005')
>> INSERT Indresults
>> (AthleteID,EventID,MeetingID,ResultTypeID,Result,ResultDate) VALUES
>> (1,1,1,1,'01/01/1900 00:00:37.750','01/01/2005')
>> INSERT Indresults
>> (AthleteID,EventID,MeetingID,ResultTypeID,Result,ResultDate) VALUES
>> (1,2,1,1,'01/01/1900 00:01:35.750','01/01/2005')
>> INSERT Indresults
>> (AthleteID,EventID,MeetingID,ResultTypeID,Result,ResultDate) VALUES
>> (1,2,1,1,'01/01/1900 00:01:36.750','01/01/2005')
>> INSERT Indresults
>> (AthleteID,EventID,MeetingID,ResultTypeID,Result,ResultDate) VALUES
>> (1,2,1,1,'01/01/1900 00:01:37.750','01/01/2005')
>
> Try:
> select
> EventID,min(Result)
> from Indresults
> group by EventID
>
This works OK, but how do I get a reference to the resultID for each result
that I can join other tables on ? If I do:

select
EventID,ResultID,min(Result)
from Indresults
where AthleteID=1
group by EventID,ResultID

This will return multiple references for each EventID

Sure I am missing something obvious here... further help woulld be
appreciated.

Nic


From: Nic on 23 Jun 2007 17:02
Works great, Many thanks !!
<markc600(a)hotmail.com> wrote in message
news:1182502556.335847.230250(a)p77g2000hsh.googlegroups.com...
>
> DECLARE @AthleteID int
>
> SET @AthleteID=1;
>
> WITH CTE AS(
> SELECT AthleteID,
> EventID,
> MeetingID,
> ResultTypeID,
> Result,
> ResultDate,
> ROW_NUMBER() OVER(PARTITION BY EventID ORDER BY Result) as rn
> FROM Indresults
> WHERE AthleteID=(a)AthleteID)
> SELECT AthleteID,
> EventID,
> MeetingID,
> ResultTypeID,
> Result,
> ResultDate
> FROM CTE
> WHERE rn=1
>

 | 
Pages: 1
Prev: FakeWorkTable error
Next: COUNT and AVG problem