CODEWITHSUNDEEP
sqllinqt-sql
John x
John x

Reputation: 4031

Get the first record from the result set

enter image description here

I want to only select the highlighted records. how can i do it in sql and preferably in linq. there is a separate table Emp with EmployeeID as PK. here is the schema for my tables

enter image description here

Upvotes: 3

Views: 3176

Answers (4)

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58431

I could be way off but I believe following statement satisfies your requirements

SELECT *
FROM   (
         SELECT e.EmployeeID, h.FromDate, h.ToDate
                , rn = ROW_NUMBER() OVER (PARTITION BY e.EmployeeID ORDER BY DesignationID DESC)
         FROM   employee e
                INNER JOIN history h ON h.EmployeeID = e.EmployeeID
       ) eh
WHERE rn = 1

Upvotes: 3

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

Not sure what you're looking for, but try something like this (no subqueries needed and should work on most DBMS, although it looks like your running SQL Server):

select t1.DisignationHistoryIDs, t1.employeeId, t1.fromDate from history t1
left join history t2
on t1.employeeId = t2.employeeId and t1.fromDate > t2.fromDate
where t2.fromDate is null

This should work to get the oldest fromDate. To get the newest from date just change the > for a <.

Upvotes: 1

user359040
user359040

Reputation:

Try:

select * from
(select t.*, 
        row_number() over (partition by EmployeeID order by FromDate) as rn) sq
where rn = 1

Upvotes: 2

njr101
njr101

Reputation: 9619

Can't see exactly what you're asking for. The highlighted ID's are just unique occurrences. So you can do this easily with:

SELECT DISTINCT EmployeeID FROM MyTable

Upvotes: 1

Related Questions