retrive last date

[altarek]

I have table p_id
I want to have the last date from duplicte cid

at this table i want to have dates (2010-5-6,2010-7-5)for duplicted cid(1,3)
and year(2010-3-2) for cid(2)
+--+----------+
|cid|did |
+--+---------+
| 1 | 2010-5-6|
| 2 | 2010-3-2|
| 3 | 2010-7-5|
| 3 | 2009-5-2|
| 1 | 2009-5-4|
+--+----------+

any help?

[guido2004]

So you want the MAX date for each cid?

[altarek]

Yes ,or the last row inserted for each cid

[guido2004]

If the last inserted row for that id has the biggest date, you better stick with the MAX(did)

Check out the link I posted, and try to figure out how it works. If you can't get it to work, post your query here and we'll help you out.

[altarek]

SELECT con.name,con.con_id,con.constat,MAX(part.pardate) AS mxyear FROM part,con WHERE part.con_id=con.con_id  GROUP BY part.con_id

this query retrieve the max date and the others from another row

if there is another way to retrieve last row for the same id

[r937]

i think this is what you're looking for...

SELECT con.name
     , con.con_id
     , con.constat
     , part.pardate
     , part.other_columns
  FROM con
INNER
  JOIN ( SELECT con_id
              , MAX(pardate) AS max_pardate
           FROM part
         GROUP
             BY con_id ) AS m
    ON m.con_id = con.con_id
INNER
  JOIN part
    ON part.con_id = m.con_id 
   AND part.pardate = m.max_pardate

it's the other_columns that makes this a challenging problem

this is usually called the "row having the groupwise max" -- in other words, your con_id is the grouping column, MAX(pardate) determines the latest row, and then you must join back to the part table to get the other columns

see also this recent thread which had the same problem

[altarek]

Thanks this query works good