CODEWITHSUNDEEP
sqlsql-server
Srinivasan
Srinivasan

Reputation: 12040

How to select latest record of particular details

I have data in SQL server table like below,

I have data like below, 

id  Cust_id   Name  Qty    Trans_date
_______________________________________

1   101     Srini   10    01-May-2018
2   101     Srini   20    01-May-2018
3   102     Arun    100   01-May-2018
4   102     Arun    200   01-May-2018
5   101     Srini   10    02-May-2018
6   101     Srini   30    02-May-2018

I want resultsets below,

5   101     Srini   10    02-May-2018
6   101     Srini   30    02-May-2018
3   102     Arun    100   01-May-2018
4   102     Arun    200   01-May-2018

I need query to get the latest trans date details. Srini's latest record is on 02nd May and Arun's latest is 01st May.

Upvotes: 0

Views: 76

Answers (7)

paparazzo
paparazzo

Reputation: 45096

DENSE_RANK() 

select * 
from ( select * 
            , DENSE_RANK() over (partition by custid order by Trans_date desc) rn
         from table
     ) t
where t.rn = 1

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can just use subquery :

select t.*
from table t
where Trans_date = (select max(Trans_date) 
                    from table t1
                    where t1.cust_id = t.cust_id
                   );

Upvotes: 0

vivekananda
vivekananda

Reputation: 7

Use this one

SELECT * 
FROM   TABLE 
WHERE  TRANS_DATE = (SELECT Max(TRANS_DATE) 
                     FROM   TABLE) 
       AND NAME = "YOURCHOICE"

Upvotes: 0

pacreely
pacreely

Reputation: 1931

Alternative solution not using Ranking functions.

this gives a different execution plan to other solutions and may perform better in your environment.

CREATE TABLE #TMP(id INT, 
                 Cust_id SMALLINT,  
                 Name  VARCHAR(50) ,
                 Qty SMALLINT,    
                 Trans_date DATE)

INSERT INTO #TMP VALUES 
( 1,101,'Srini', 10 ,'01-May-2018' ),
( 2,101,'Srini', 20 ,'01-May-2018' ),
( 3,102,'Arun' , 100,'01-May-2018' ),
( 4,102,'Arun' , 200,'01-May-2018' ),
( 5,101,'Srini', 10 ,'02-May-2018' ),
( 6,101,'Srini', 30 ,'02-May-2018' )


;WITH cte AS (
SELECT
    Cust_id,MAX(Trans_Date) MaxTrans
FROM    
    #TMP
GROUP BY Cust_id
)
SELECT
    T.*
FROM
    #TMP T
        INNER JOIN cte C
            ON
            T.Cust_id = C.Cust_id
            AND
            T.Trans_date = C.MaxTrans

Upvotes: 0

Sreenu131
Sreenu131

Reputation: 2516

Try this

;WITH CTE(id,  Cust_id ,  Name  ,Qty,    Trans_date)
AS
(
SELECT 1,101,'Srini', 10 ,'01-May-2018' UNION ALL
SELECT 2,101,'Srini', 20 ,'01-May-2018' UNION ALL
SELECT 3,102,'Arun' , 100,'01-May-2018' UNION ALL
SELECT 4,102,'Arun' , 200,'01-May-2018' UNION ALL
SELECT 5,101,'Srini', 10 ,'02-May-2018' UNION ALL
SELECT 6,101,'Srini', 30 ,'02-May-2018' 
)
SELECT ID, 
       CUST_ID, 
       NAME, 
       QTY, 
       TRANS_DATE 
FROM   (SELECT *, 
               ROW_NUMBER() 
                 OVER( 
                   PARTITION BY CUST_ID 
                   ORDER BY TRANS_DATE DESC) AS Rnk 
        FROM   CTE)Dt 
WHERE  Dt.RNK <= 2

Result

ID  CUST_ID NAME    QTY  TRANS_DATE
------------------------------------
6   101 Srini       30   02-May-2018
5   101 Srini       10   02-May-2018
4   102 Arun        200  01-May-2018
3   102 Arun        100  01-May-2018

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

This problem can be handled well by the difference in row number method. This method is fairly robust, and can tolerate a situation where separate islands of customer records all have the same date. To see why this works, explore the demo; it is not easy to explain in words.

cte1 AS (
    SELECT *,
        ROW_NUMBER() OVER (ORDER BY id) -
            ROW_NUMBER() OVER (PARTITION BY Cust_id ORDER BY id) diff
    FROM yourTable
),
cte2 AS (
    SELECT *,
        DENSE_RANK() OVER (PARTITION BY Cust_id ORDER BY diff DESC) rank
    FROM cte1
)

SELECT id, Cust_id, Name, Qty, Trans_date
FROM cte2
WHERE rank = 1;

Demo

Upvotes: 1

Tanveer Singh Bhatia
Tanveer Singh Bhatia

Reputation: 426

DENSE_RANK() Function should worrk

Select * from ( select * 
        , DENSE_RANK() over (partition by cust_id order by Trans_date DESC) row_n from table_name
 ) A
Where A.row_n = 1

Upvotes: 0

Related Questions