CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
timmack
timmack

Reputation: 590

How to select the last latest records with associated IDs from the excluded row on the query?

I have a sample table below and a sql statement that I used but I need some help on some certain issue on this. Based on the table below I need to get only the last latest results based on MessageIDs that are associated with the UseEmail of the excluded row. By using the statement sql below, it will give all the results of one set of records associated with the MessageID but all I need is the latest results based on MessageID ordered by Id in Desc order. For further explanations, look through all the details below. Thanks!

Sql Statement used so far,

select  Id,UserEmail,SellerEmail,Messages,UserName,MessageID 
from [MessagesTab] 
where MessageID in
( 
    select  MessageID 
    from [MessagesTab]
    where UserEmail = @useremail
) 
and UserEmail <> @useremail  Order by Id Desc;

Table

 Id      UserEmail                SellerEmail         Messages             UserName       MessageID
 19     [email protected]    [email protected]   Hi!...               Katie          13d6c0c9
 20     [email protected]         [email protected]   Hello.               Seller's Name  13d6c0c9  
 21     [email protected]    [email protected]   Where are you?       Katie          13d6c0c9  
22     [email protected]        [email protected]   When will you call?  Jenny          69e37491  
23     [email protected]          [email protected]   I'll meet you there. Seller's Name  13d6c0c9  
24     [email protected]          [email protected]    Ok. Let's go.        Seller's Name  69e37491

@useremail = [email protected] - It will give a result of 

23     [email protected]    [email protected]      I'll meet you there.    Seller's Name      13d6c0c9
20     [email protected]    [email protected]      Hello.                  Seller's Name      13d6c0c9

but I only need the last latest results based on MessageID which is ordered by Id in desc order like this below

23     [email protected]    [email protected]      I'll meet you there.    Seller's Name      13d6c0c9

same as @useremail = [email protected] - It will give a result of 

22      [email protected]     [email protected]     When will you call?   Jenny       69e37491
21      [email protected]  [email protected]     Where are you?        Katie       13d6c0c9
19      [email protected]  [email protected]     Hi!...                Katie       13d6c0c9

but I only need the last latest results based on MessageID which is ordered by Id in desc order like this below

22         [email protected]   [email protected]   When will you call?    Jenny       69e37491
21      [email protected]   [email protected]   Where are you?         Katie       13d6c0c9

Upvotes: 0

Views: 76

Answers (2)

Felix Pamittan
Felix Pamittan

Reputation: 31879

Using ROW_NUMBER() OVER(PARTITION BY MessageID ORDER BY ID DESC) will do the trick:

SELECT 
    Id, UserEmail, SellerEmail, Messages, MessageID
FROM (
    SELECT *,
        RN = ROW_NUMBER() OVER(PARTITION BY MessageID ORDER BY ID DESC)
    FROM MessagesTab
    WHERE 
        UserEmail <> @useremail
        AND MessageID IN(
            SELECT MessageID
            FROM MessagesTab
            WHERE UserEmail = @useremail
        )
) t
WHERE RN = 1
ORDER BY ID DESC

Upvotes: 2

anwaar_hell
anwaar_hell

Reputation: 776

    select * from (
select  Id,UserEmail,SellerEmail,Messages,UserName,MessageID ,
row_number () over (partition by UserEmail order by ID desc  ) as rnm
from [MessagesTab] 
where MessageID in
( 
    select  MessageID 
    from [MessagesTab]
    where UserEmail = '[email protected]'
) 
and UserEmail <> '[email protected]' ) x
where rnm =1;

Upvotes: 2

Related Questions