Jump to content

Get The Latest Date And Summery


chefhung
 Share

Recommended Posts

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?

Edited by chefhung
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.?

Link to comment
Share on other sites

(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.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...