The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

how to optimize this SQL statement ?

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 :

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.

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.

What you're doing should be able to be done in one select. You don't need to select the sessionID since you obviously already know what it is. If you want to loop through the sessions, add the session to the select and to the group by portions...

** WARNING ** Code hasn't been tested, so there may be some syntax errors (especially with the convert portion), so test thoroughly....