CODEWITHSUNDEEP
phpmysql
Compy
Compy

Reputation: 1177

Selecting mysql records with latest timestamp

Ok, so I have a table like this:

ID    Number     State     Gender         tstamp
==----======----=======----======----===================
1     01        Open       Male      2013-04-06 10:27:26
2     01        Open       Female    2013-04-07 10:28:23
3     04        Open       Male      2013-04-07 11:23:13
4     04        Closed     Male      2013-04-07 11:26:45
5     01        Open       Male      2013-04-07 12:23:21
5     04        Open       Female    2013-04-08 13:27:55
6     04        Open       Male      2013-04-08 13:28:53
7     01        Open       Female    2013-04-09 13:29:23
8     01        Open       Female    2013-04-09 13:30:36

and I need to generate some SQL/PHP to count the Male records with the state Open for each number. I then need to check wether or not the records gender has been changed previously and ignore the previously set state. So as above when record number 01 changed from Male to Female, back to Male again and then once more back to female I need it to ignore the old genders and just count the male record so it would only count:

6     04        Open       Male      2013-04-08 13:27:65
8     01        Open       Female    2013-04-09 13:27:65

and would then return 2 as the count.

I can't get my head around how I would do this, I tried a while loop which checked if the state was the same and the gender was the same as the last records but I couldn't get it to do what I wanted. I'm sure if somebody shows me the solution then It'll all suddenly make sense.

I would seriously appreciate some help with this!

Thanks!

Upvotes: 0

Views: 458

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

First, you need to get the last record for each number. This requires a join:

select t.*
from t join
     (select t.number, max(id) as maxid
      from t
      group by t.number
     ) tmax
     on t.id = tmax.maxid

Then, instead of selecting all the records, do the count that you want:

select t.number, sum(case when gender = 'male' and state = 'open' then 1 else 0 end) as NumMales
from t join
     (select t.number, max(id) as maxid
      from t
      group by t.number
     ) tmax
     on t.id = tmax.maxid
group by t.number

You may also be asking to a 0/1 flag for each number that says whether a male/open record ever existed. For this, you can do:

select t.number, max(case when gender = 'male' and state = 'open' then 1 else 0 end)
from t
group by t.number

Upvotes: 1

Robert
Robert

Reputation: 20286

You need something like this if I understand question well.

SELECT COUNT(*) FROM your_table_name t1  WHERE state='Open' GROUP BY ID ORDER BY tstamp DESC

or u need first make join between two same tables in this join you need select only 1 value of ID(sorted DESC by tstamp) and count these values.

Upvotes: 1

Related Questions