0

my data in the mySQL DB looks like this (but not only this 4, i have many persons which are appearing more then once, but with different qualifications and different modified dates

the data i have in my database

Selection would be something like:

SELECT * FROM table where person_id=1 GROUP BY person_id

so, if i make this selection and i group by person_id i get something like this:

my result after selection an group by

is there any possibility to group by the person id, but to say: ok, give me the last modified qualification? normally i don't have the person_id in the where SQL statement.
so i want all persons from my selection but only the result with the last modified qualification
( I hope I explained it well eonough so you understand what the problem is )

(my selection is of course much more complicated then the one i showed, this is just for showing you what I'm doing)

Joergi
  • 1,527
  • 3
  • 39
  • 82

3 Answers3

3

You can also use a subquery to return the max modified date for each person_id and then join that result to your table to get the rows with the max date:

select t1.id, 
  t1.person_id,
  t1.name,
  t1.qualification,
  t1.created,
  t1.modified,
  t1.version
from yourtable t1
inner join
(
  select person_id, max(modified) MaxMod
  from yourtable
  group by person_id
) t2
  on t1.person_id = t2.person_d
  and t1.modified = t2.maxmod
Taryn
  • 242,637
  • 56
  • 362
  • 405
  • this sounds more like what i want, i will give it a try, thanks! – Joergi Apr 19 '13 at 10:30
  • looks like it works, but the performance is so totally unacceptable, i will make a simpler SQL call - and then do the optimation in java. but thanks anyway @bluefeet – Joergi Apr 19 '13 at 13:42
  • @Joerg do you have any indexes on the table? – Taryn Apr 19 '13 at 14:09
0

If you want the last modified entry of a specific person then just do

select * from table
where person_id = 1
order by modified desc
limit 1
juergen d
  • 201,996
  • 37
  • 293
  • 362
0

EDIT

I misunderstood the question and i will update the answer

Try this (assuming you want the last modified qualification for each person):

SELECT 
   person_id,
   qualification
FROM 
   table as a
INNER JOIN (
   SELECT 
     person_id,
     MAX(modified) as za_date
   FROM 
     table
   GROUP BY 
     person_id
) as tmp 
  ON a.person_id = tmp.person_id
  AND a.modified = tmp.za_date

If you want the last modified qualification for all persons :

SELECT
   qualification
FROM
   table
WHERE modified = (
       SELECT 
           MAX(modified)
       FROM
           table  
   )
Stephan
  • 8,000
  • 3
  • 36
  • 42
  • its just a standard to format queries to have WHERE 1 AND cond1 AND cond2 its more readeable this way – Stephan Apr 19 '13 at 10:32
  • [The main purpose of "WHERE 1" (or "WHERE 1=1") seems to be dynamic filtering](http://stackoverflow.com/a/242831/17117960). I would not advise using this for all queries everywhere. It's not more readable if for no other reason than it is an extra bit of code to read that does nothing (and some people may think you were trying to do something else, but mistyped, or may not understand it). – Bernhard Barker Apr 19 '13 at 10:39
  • yeah ur right, if you have no AND conditions there no purpose in using it but for me its the force of habit :) – Stephan Apr 19 '13 at 10:41
  • 2
    Why all these `WHERE 1`? Do they serve a purpose? Why don't you add `HAVING 1` as well? – ypercubeᵀᴹ Apr 19 '13 at 11:23
  • Due to the many complaints i removed all `WHERE 1` as I've said before its a force of habit and from my pov i do not deserve down vote just for having a different query writing standard then others :) – Stephan Apr 19 '13 at 12:18