CODEWITHSUNDEEP
phpmysql
Dharmendra Singh
Dharmendra Singh

Reputation: 1226

Mysql get max row and min row from group of each row

I have MySQL table with employees attendance. first row of a day of employee treating as in time and last row of a day of employee treating as out time. I am trying to select first and last (min time and max time) from attendance table. It should give me two row sets. but my query not giving me as i expecting the result.

Table (Attendance)

Attendance Table with employees attendance (IMAGE)

My Query

select *, min(attdate) as intime, max(attdate) as outtime  from attendance where empid=1

But above query not giving me as expected result. My output should be in below image. Please suggest me the query or give me hint to achieve given output.

Attendance out put should be (IMAGE)

Upvotes: 1

Views: 250

Answers (2)

Mahmood Sanjrani
Mahmood Sanjrani

Reputation: 110

this can be done by sub queries in where conditions.

SELECT * FROM attendance AS c WHERE empid=1 and (
attdate=( select min(attdate) from attendance where attendance.empid=c.empid )
 or attdate=( select max(attdate) from attendance where attendance.empid=c.empid )
);

Upvotes: 1

sagi
sagi

Reputation: 40471

Unfortunately, MySQL doesn't offer window functions, so it's a bit more difficult here. You can use exists : 

Select * from yourtable t
Where not exists (select 1 from yourtable s
                              Where t.empid = s.empid and
                                         (s.attndate < t.attndate or s.attndate > t.attndate))

Though it seems you need to add another condition t.date = s.date unless you have only 1 day records stored there

Upvotes: 0

Related Questions