Get The Latest Date And Summery

[chefhung]

name | start date | end date | summary-------------------------------------------------------------------------------------------danny | 03/1/2009 | 30/1/2009 | danny's message adanny | 03/2/2009 | 30/2/2009 | danny's message bdanny | 03/3/2009 | 30/3/2009 | danny's message cdanny | 03/4/2009 | 30/4/2009 | danny's message ddanny | 03/5/2009 | 30/5/2009 | danny's message edanny | 03/6/2009 | 30/6/2009 | danny's message fchan | 03/1/2009 | 30/1/2009 | chan's message achan | 03/2/2009 | 30/2/2009 | chan's message bchan | 03/3/2009 | 30/3/2009 | chan's message cchan | 03/4/2009 | 30/4/2009 | chan's message dchan | 03/5/2009 | 30/5/2009 | chan's message echan | 03/6/2009 | 30/6/2009 | chan's message f...-------------------------------------------------------------------------------------------i want to print out latest date, and to match name and summary.how can i do it?

[justsomeguy]

It depends which database you're using, but order by the latest date in descending order and only select the first record.

[chefhung]

It depends which database you're using, but order by the latest date in descending order and only select the first record.

SELECT resources.name, schedules.scheduletitle, reservations.summary, reservations.start_date, reservations.end_dateFROM cwlab.resources, cwlab.schedules, cwlab.reservationsWHERE schedules.scheduletitle = 'dm2d' AND schedules.scheduleid = resources.scheduleid AND schedules.scheduleid = reservations.scheduleid AND resources.machid = reservations.machidthat query are listing very thing, i just could not get the latest one.

[justsomeguy]

You need to add an ORDER BY clause at the end. If your database supports it, you could also add a LIMIT clause to only get 1 record instead of all of them.

[chefhung]

You need to add an ORDER BY clause at the end. If your database supports it, you could also add a LIMIT clause to only get 1 record instead of all of them.

"order by resverations,name DSEC Limit 1"I try that befor, it only get 1 row, not 1 row per group."SELECT start_date, end_date, summary, resid, machidFROM cwlab.reservations as r2WHERE r2.scheduleid = 'cwe0000003640000' and r2.start_date=(SELECT max(start_date) from reservations as r3 WHERE r3.machid =r2.machid and r3.scheduleid = r2.scheduleid)"this script can print out 1 row per group. but the database is multi table, i don't know know join that script in to first script.

[chefhung]

SELECT s1.scheduletitle, r2.start_date, r2.end_date, r2.summary, r2.machid, ee1.nameFROM schedules as s1 LEFT OUTER JOIN resources as ee1 ON s1.scheduleid = ee1.scheduleid LEFT OUTER JOIN reservations as r2 ON ee1.machid = r2.machidWHERE s1.scheduleid = 'cwe0000003640000' -- AND (r2.end_date IS NULL or r2.end_date IS not NULL ) and r2.start_date=(SELECT max(start_date) from reservations as r3 WHERE r3.machid = r2.machid and r3.scheduleid = r2.scheduleid) UNION SELECT s1.scheduletitle, r2.start_date, r2.end_date, r2.summary, r2.machid, ee1.nameFROM schedules as s1 LEFT OUTER JOIN resources as ee1 ON s1.scheduleid = ee1.scheduleid LEFT OUTER JOIN reservations as r2 ON ee1.machid = r2.machidWHERE s1.scheduleid = 'cwe0000003640000' AND r2.end_date IS NULL=============================================i got everting now, but i sorting it.?

[chefhung]

(SELECTs1.scheduletitle,r2.start_date,r2.end_date,r2.summary,r2.machid,ee1.nameFROMschedules as s1LEFT OUTER JOIN resources as ee1ON s1.scheduleid = ee1.scheduleidLEFT OUTER JOIN reservations as r2ON ee1.machid = r2.machidWHEREs1.scheduleid = 'cwe0000003640000'-- AND (r2.end_date IS NULL or r2.end_date IS not NULL )and r2.start_date=(SELECT max(start_date)from reservations as r3WHEREr3.machid = r2.machidand r3.scheduleid = r2.scheduleid))UNION(SELECTs1.scheduletitle,r2.start_date,r2.end_date,r2.summary,r2.machid,ee1.nameFROMschedules as s1LEFT OUTER JOIN resources as ee1ON s1.scheduleid = ee1.scheduleidLEFT OUTER JOIN reservations as r2ON ee1.machid = r2.machidWHEREs1.scheduleid = 'cwe0000003640000'AND r2.end_date IS NULL)Order by name========================================everthing all good.finally finish my project HA~!, hope that script can help some one like me.