CODEWITHSUNDEEP
sql-server-2000distinct
hhead
hhead

Reputation: 31

SQL Server 2000 query

I have a created a few tables containing multiple records from several users so I can simulate circumstances.

I created the following query:

SELECT 
    a.celid, a.callid, a.active, a.messagetext,
    b.jactive, a.cel_time, c.username, a.muserid 
FROM level2 a, calls b , login c 
WHERE a.callid = b.jid 
  AND a.muserid = c.loginid 
  AND b.jid = 92 
  AND a.win = 0 
  AND b.userid = 12 
ORDER BY 
    cel_time ASC

and got the following as result

545 92  2   hello1  2   2011-09-18 16:32:17.000 phil01  21
546 92  1   hello2  2   2011-09-18 16:42:38.000 phil01  21
547 92  2   hello3  2   2011-09-18 16:59:08.000 danny   16
548 92  1   hello4  2   2011-09-18 20:46:21.000 phil01  21
549 92  1   hello5  2   2011-09-18 20:47:16.000 phil01  21  
550 92  1   hello6  2   2011-09-19 19:32:15.000 phil01  21  
551 92  1   hello7  2   2011-09-19 19:34:14.000 phil01  21

but I actually want this result to be distinct on muserid and return only return two rows.

I have studied distinct value description but can not seem to get this accomplished.

How would I accomplish this?

Upvotes: 0

Views: 61

Answers (1)

Saket
Saket

Reputation: 46137

Use this SQL:

SELECT 
    a.celid, a.callid, a.active, a.messagetext,
    b.jactive, a.cel_time, c.username, a.muserid 
FROM level2 a
JOIN calls b ON a.callid = b.jid 
JOIN login c ON a.muserid = c.loginid 
JOIN 
  (SELECT l2.muserid, MAX(l2.cel_time) as max_time 
   FROM level2 l2 
   GROUP BY l2.muserid) d ON (d.muserid = a.muserid AND a.cel_time = d.max_time)
WHERE b.jid = 92 
  AND a.win = 0 
  AND b.userid = 12

Upvotes: 1

Related Questions