1

(question edited) My table has id,status,date,sequence

Situation: Get ids which satisfies:

  • date is max, not more than today's and status is A
  • if more than 1 status' with same date then get id only if it has max sequence

I am writing MYSQL in dbVisulizer.

edit: trying with query:

select id, max(date), max(sequence) from 
table
where
table.date<=now() and status='A'
order by date desc,sequence desc

It sounds like I am just asking direct question without trying anything by myself, but for this situation I am completely stuck, I tried with case when but couldn't really accomplish any good, any starting point would be appriciated.

Anil Bhaskar
  • 3,718
  • 4
  • 33
  • 51

2 Answers2

1
select id, max(date), max(sequence) from 
table
where
table.date<=now() and status='A'
order by date desc,sequence desc
group_by id;

This should give you the desired results. Adapt table and field names to your table and fields.

Lelio Faieta
  • 6,457
  • 7
  • 40
  • 74
  • thanks @Lelio `now()` and `limit` are not supported(dbvisualizer 6.5.1), also I guess your query will limit the result to just one record. Rest looks promising..not sure it is correct – Anil Bhaskar Jun 22 '15 at 12:37
  • also needs to `group by` on `id`, to use `max` (an aggregate function) – Anil Bhaskar Jun 22 '15 at 12:41
  • ehm... this is not a problem of the tool you are using. Syntax is related to the db engine you are using. now() and limit are supported by mysql and are basic functionalities. Anyway you can skip the limit 1 part and pass the date as a parameter instead of using noww – Lelio Faieta Jun 22 '15 at 12:41
  • group_by is used to return more than one row (one for each project_id in your case) in case of aggregate functions like max. Without a sqlfiddle or the exact table and data structure I can just give you some hints on how to get to your point – Lelio Faieta Jun 22 '15 at 12:42
  • getting error `id is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause` – Anil Bhaskar Jun 22 '15 at 12:45
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/81179/discussion-between-anil-bhaskar-and-lelio-faieta). – Anil Bhaskar Jun 22 '15 at 12:48
0
select id from 
table
where
table.date<now() and table.status='A'
order by date desc,sequence desc
limit 1;

You should check it for mistakes by yourself.

Light
  • 375
  • 4
  • 11