I use this query to select the results out of a table depending on the latest date as submitted by a unique user. (data inserted into table automatically as a user fails to contact another, then the user can access the data in another page to view the contacts he/she has missed) query works fine, but plz advice if there's another way to write it so as to make it faster.
FROM x INNER JOIN y ON x.ID = y.ID
WHERE aID = 'passed string'
AND yID IN (SELECT MAX(yID) AS yID
FROM y AS IM2
WHERE aID = 'passed string' GROUP BY CONVERT(varchar,Date,101),aID,bID)
Last edited by GoldCoast_Nerd; 01-21-2007 at 08:46 PM.
Thanks for the reply, but i can't select max ID becoz if a user misses being contacted by another user two consecutive days, then i have to show that this user has to appear in the missed contact list twice and sorted by date i.e. latest date first. however i have to group becoz if i just select all the missed contacts for a certain user, then they'll c all users that tried to contact them and if a user tries multiple times in one day, they'll be shown in the list multiple times as well. thus i want to show only one contact attempt per day. the primary key is just a unique ID for the table.....