CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Citizen SP
Citizen SP

Reputation: 1411

group results by min datetime

I try to get the first (lowest) start date of a employee:

SELECT a.StartDate, a.EmpID, b.fullname FROM Employees a 
LEFT JOIN dbo.info b ON a.EmpID = b.EmpID
WHERE 
    type = 800 
    GROUP By EmpId
    ORDER BY fullname

The result is similar to this:

StartDate | EmpId
1992-12-01 00:00:00.000 | 7
2014-01-01 00:00:00.000 | 7
2015-01-01 00:00:00.000 | 7
1992-12-01 00:00:00.000 | 10
2014-01-01 00:00:00.000 | 10
2015-01-01 00:00:00.000 | 10
1992-01-01 00:00:00.000 | 16
2014-01-01 00:00:00.000 | 16
2015-01-01 00:00:00.000 | 16

To get the lowest date of each employee I tried this, but it resulted in an error.

SELECT MIN(a.StartDate), a.EmpID, b.fullname FROM Employees a 
LEFT JOIN dbo.info b ON a.EmpID = b.EmpID
WHERE 
    type = 800 
    GROUP By EmpId
    ORDER BY fullname

What is the correct query to get the result as below:

StartDate | EmpId
1992-12-01 00:00:00.000 | 7
1992-12-01 00:00:00.000 | 10
1992-01-01 00:00:00.000 | 16

Upvotes: 1

Views: 829

Answers (5)

Mureinik
Mureinik

Reputation: 311338

You want a result row per EmpID, so that's the column you should group by:

SELECT   MIN(StartDate), EmpID 
FROM     Employees
WHERE    type = 800 AND EmpID BETWEEN 1 AND 500
GROUP BY EmpID

Upvotes: 1

Tomer W
Tomer W

Reputation: 3443

SELECT MIN(StartDate), EmpID FROM Absences
WHERE type = 800 
AND EmpID BETWEEN 1 AND 500
GROUP BY StartDate

your group by is on the wrong field.

GROUP BY EmpID

Upvotes: 0

kasim
kasim

Reputation: 356

SELECT DISTINCT a.EmpID, b.fullname, a.StartDate FROM Employees a 
LEFT JOIN dbo.info b 
ON a.EmpID = b.EmpID
WHERE type = 800    
ORDER BY a.StartDate, b.fullname ASC

Upvotes: 0

Javaluca
Javaluca

Reputation: 857

SELECT a.StartDate, a.EmpID, b.fullname 
FROM Employees a 
LEFT JOIN dbo.info b ON a.EmpID = b.EmpID
WHERE type = 800 
AND NOT EXISTS ( SELECT 'a'
                 FROM Employees a2
                 WHERE a.EmpID = a2.EmpID
                 AND a2.StartDate < a.StartDate
               )

Upvotes: 2

Laxman Parmar
Laxman Parmar

Reputation: 120

Use this Query, This will stop repeating data

  SELECT Distinct StartDate, EmpID FROM Employees

Upvotes: 0

Related Questions