SQL SERVER: Query to get the last instance of an ID but return the first instance of that ID

[dexterdg]

[pre]
callid calling_pty dialed_num disposition dispvdn segment ucid
3862073 25555366 1001 1 1001 1 10000080561437527378
3862073 291113 1015 2 1015 2 10000080621437527416
3862074 635444419 1001 3 1001 1 10000081311437527892
3862075 25555596 1001 1 1001 1 10000081211437527803
3862075 26669 1014 3 1014 2 10000081231437527820
3862076 23044442 1009 2 1009 1 10000080961437527654
3862079 632444419 1001 1 1001 1 10000081361437527904
3862089 0111808 1001 1 1001 1 10000081671437528068
3862091 02366674 1001 1 1001 1 10000081011437527679
3862091 26666 1015 2 1015 2 10000081091437527712
3862097 63255529 1001 1 1001 1 10000081721437528139
3862097 29111 1014 3 1014 2 10000081771437528168
[/pre]

The table above I are logs of calls.
I need to get the specific record and display it with the following conditions:
1. Must be disposition = 3 [basis]
2. If the record has segment = 3 or segment = 2, the record to be displayed is the one with segment = 1, Hence if none, then display the current record set.
3. Records are grouped by called as the id for the transaction.

Now my dilemma is how can I validate my record set while still on the process of fetching data as stated on the requirement #2.
I tried nested Select and Joins but cant come up to the code to check if that record with disposition = 3 has a previous segment(2 or 1). just after that I can come up with the list.

With the above sample the result should be:
[pre]
3862074 635444419 1001 3 1001 1 10000081311437527892
3862075 25555596 1001 1 1001 1 10000081211437527803
3862097 63255529 1001 1 1001 1 10000081721437528139
[/pre]

Im pretty much stuck right now on my development because of this.
Thanks in advance!

Dexter

 

[djj55]

Have you tried an EXISTS? WHERE EXISTS (SELECT x FROM table1 WHERE segment IN (3, 2) AND ...)

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[dexterdg]

Hi! for reference:
Found out the answer for this one using sub-query to validate

SELECT callid, 
       (SELECT calling_pty 
        FROM Log l
        WHERE Log.callid = l.callid AND
              segment = (SELECT MIN(segment) 
                         FROM Log l2
                         WHERE l.callid = l2.callid) 
       )[calling_pty], 
       dialed_num, disposition, dispvdn, segment, ucid
FROM Log
WHERE disposition = 3

courtesy of:
PM 77-1