CODEWITHSUNDEEP
sql-servermax
see sharp
see sharp

Reputation: 79

Max record in sql aginst ID

I have this query 

SELECT 
    MAX([ID]),
    [sr].[EmpId],
    [vw].[EmpName],
    [sr].[IAmount]
FROM 
    [SRev] [sr]
INNER JOIN 
    [vwemp] [vw] ON [sr].[EmpId] = [vw].[EmpId]
INNER JOIN 
    [SalaryType] [st] ON [st].[SalaryType] = [sr].[SalaryType]
WHERE 
    [sr].[EmpId] IN (125553)
GROUP BY 
    [sr].[EmpId], [vw].[EmpName], [sr].[IAmount];

This query returns a result set like this:

(No column name)    EmpId   EmpName     IAmount
-----------------------------------------------
1687106             12553   BABC         -8500
2136112             12553   BABC           936
1782854             12553   BABC          1225
1687106             12553   BABC          3000
2628907             12553   BABC         22839

I only want max against record against each empid

(No column name)    EmpId    EmpName    IAmount
-----------------------------------------------
2628907             12553    BABC        22839

Upvotes: 0

Views: 29

Answers (1)

Thom A
Thom A

Reputation: 95830

Sounds like you want to use ROW_NUMBER and a CTE:

WITH CTE AS(
    SELECT ID, --not sure of the lias for this one
           sr.EmpID,
           vw.EmpName,
           sr.IAmount,
           ROW_NUMBER() OVER (PARTITION BY sr.EmpId ORDER BY ID DESC) AS RN
    FROM SRev sr
         INNER JOIN vwemp vw ON sr.EmpId = vw.EmpId
         INNER JOIN SalaryType st ON st.SalaryType = sr.SalaryType
     WHERE sr.EmpId = 125553)
SELECT ID,
       EmpId,
       EmpName,
       IAmount
FROM CTE
WHERE RN = 1;

Note this is untested, as I don't have any consumable sample data or DDL (the result set above it from your query).

Upvotes: 1

Related Questions