CODEWITHSUNDEEP
sqlsql-serversql-server-2008
agarwal_achhnera
agarwal_achhnera

Reputation: 2456

how to fetch top distinct data in sqlserver

I have a table named employee_salary, with three columns: empsal_id, empsal_name and empsal_sal. The data is as follows:

empsal_id   empsal_name   empsal_sal

1           dilip         14000
2           santosh       20000
3           amit          32000
4           dilip         22000
5           amit          38000
6           santosh       25000
7           dilip         30000

The empsal_id is an Identity column with a Seed and an Increment of 1, and is the Primary Key. I want to return the name and current salary of each employee. Salary can decrease as well as increase, so current does not necessarily mean highest.

So I need the following output:

empname     emp_sal
dilip       30000
amit        38000
santosh     25000

I am using Microsoft SQL Server, and I have to do this in a single query.

Upvotes: 0

Views: 551

Answers (4)

gkrogers
gkrogers

Reputation: 8356

This query will return each employee, along with their highest salary:

    SELECT
        empsal_name, MAX(empsal_sal)
    FROM
        employee
    GROUP BY
        empsal_name

This query will return each employee, along with their current salary (i.e. the salary with the highest empsal_id:

    SELECT
        empsal_name, empsal_sal
    FROM
        employee e1
    WHERE
        empsal_id =
            (SELECT MAX(empsal_id)
             FROM employee e2
             WHERE e1.empsal_name=e2.empsal_name)

Personally, I think you would be better off using an effective date column (e.g. empsal_effectivedate) to determine which record is the most current, so this query will return each employee, along with their current salary (i.e. the salary with the most recent empsal_effectivedate), assuming there is an empsal_effectivedate field:

    SELECT
        empsal_name, empsal_sal
    FROM
        employee e1
    WHERE
        empsal_effectivedate =
            (SELECT MAX(empsal_effectivedate)
             FROM employee e2
             WHERE e1.empsal_name=e2.empsal_name)

Upvotes: 2

praveen
praveen

Reputation: 12271

You need a date field to determine the latest inserted row . In case if the table is linked to some other table which has date column in it .Then its pretty easy to fetch the current data . For Example 

    Employee Table 
    {
    EmpName varchar(30) PK,
    EmpAddress varchar(255) ,
    Company varchar(30),
    CurrentTimeStamp Datetime
    }

    Salary Table 
    {
    EmpName varchar(30) FK,
    EmpSalary int
    }

To get the Latest record use the CTE function 

    With LatestSal(EmpName ,EmpSalary)
    AS
    (
    Select row_number() over (PARTITION BY b.[EmpName], order by CurrentTimestamp DESC) as seq
    b.EmpName,b.EmpSalary
    From Employee as a,
    Salary as b
    on a.[EmpName]=b.[EmpName]
    ) 
    Select EmpName,EmpSalary 
    from LatestSal
    where seq=1

Upvotes: 0

John Woo
John Woo

Reputation: 263703

SELECT empname, MAX(emp_sal) 
FROM   employee
GROUP BY  empname

UPDATED:

SELECT DISTINCT EmpA.empname, 
                EmpA.emp_sal
FROM Employee AS EmpA 
     INNER JOIN ( SELECT  EmpName, MAX(recID) AS recid 
                  FROM Employee 
                  GROUP BY EmpName
                ) AS EmpB ON EmpA.recid = EmpB.recid;

Upvotes: 1

Gabriele Petrioli
Gabriele Petrioli

Reputation: 195982

Assuming there is also an ID in the table and also assuming that the larger this ID is the most recent the salary is for that employee you can do 

SELECT DISTINCT
    e.empname,
    (SELECT TOP 1 
        emp_sal 
    FROM 
        employee s
    WHERE 
        s.empname = e.empname 
    ORDER BY 
        recid DESC) AS emp_sal
FROM
    employee e

(also assuming that empname is unique for an employee)

because of the many assumptions though : you should probably post all the columns of the table and what they mean ..

Upvotes: 1

Related Questions