Hi,

I've been helped a number of times here regarding to SQL statements, my thx again :-)

This time it is no different.
I have a SQL statement that works but I want it to run faster (now takes about 3 sec.) an design it better. The DB is SQL Server.

Alright one table is used here : Participations.
It contains phone numbers bound to a specific session (broadcast on tv) and some info about when they called. The nr. of rows has a max. of about 10000, because each session is being backupped and then deleted.
Table SQL :
Code:
CREATE TABLE [dbo].[Participations] (
 [Id] [int] IDENTITY (1, 1) NOT NULL ,
 [SessionId] [int] NOT NULL ,
 [DateVal] [char] (10) NOT NULL ,
 [TimeVal] [char] (10) NOT NULL ,
 [szTelephone] [char] (10) NOT NULL ,
 [Post_FLAG] [int] NOT NULL 
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Participations] WITH NOCHECK ADD 
 CONSTRAINT [PK_Participations] PRIMARY KEY  CLUSTERED 
 (
  [ID]
 )  ON [PRIMARY] 
GO
ALTER TABLE [dbo].[Participations] ADD 
 CONSTRAINT [DF_Participations_Post_FLAG] DEFAULT (1) FOR [Post_FLAG]
GO
 CREATE  INDEX [Participations_tel] ON [dbo].[Participations]([szTelephone]) ON [PRIMARY]
GO
 CREATE  INDEX [Participations_time] ON [dbo].[Participations]([TimeVal]) ON [PRIMARY]
GO
To be clear, I didn't design the table, otherwise I would have used DateTime, but it's irrelevant for the example.

Now the SQL statement itself.
Purpose : to give me all the phone numbers for a specific session, the number of times they occurred for that session, their unique ID (pk) and the date and time they entered the system. If any phone number occurs more than once, return only the one that entered for the first time along with it's corresponding date, time and Id (pk) values.
So some of you already will notice the combining of aggregate functions and normal fields.

Now the way I did it :
Code:
SELECT C.*
FROM
(
SELECT b.szTelephone, b.ANumber, b.DateVal, b.TimeVal, a.ID, a.Session
FROM Participations A,
 (
 SELECT szTelephone, COUNT(szTelephone) as ANumber, MAX(DateVal) as DateVal, MAX(TimeVal) as TimeVal
 FROM Participations
 WHERE Session = 143 AND Post_FLAG = 0
 GROUP BY szTelephone
 ) B
WHERE A.szTelephone = B.szTelephone
AND   A.Session = 143 AND POST_Flag = 0
) C
WHERE C.ID = (SELECT MIN(ID) FROM Participations WHERE C.szTelephone = szTelephone AND Session = 143 AND Post_Flag = 0)
ORDER BY C.ID
This thing runs at 3 sec. average.
I used a subselect as table B, because I have to fetch the unique ID (and Session) for each phone number, but because I use aggregate functions in that subselect, I can't just select the Id in there, either use it in aggregate function or put it in group by, but neither of those options is what I want, hence table B.
Table C is for selecting the first ID occurred in that table.

Any improvements ? :-)

Thx in advance !