> -----Original Message----- > From: IBM Mainframe Discussion List > [mailto:IBM-MAIN@bama.ua.edu] On Behalf Of Ron Thomas > Sent: Monday, October 10, 2011 1:36 PM > To: IBM-MAIN@bama.ua.edu > Subject: SQL Query > > Hi . > > > Here is a sample data > > Plan Name Payroll date Historty > cd > aaa 2012-01-01 1 > bbb 2012-02-02 2 > bbb 2012-03-03 2 > > Plan Name Payroll date lfycd > > aaa 2012-01-01 1 > bbb 2012-02-02 2 > bbb 2012-03-03 x > > > I need to join these 2 tables and get the max payolldate date > record that matches > > Which means i need to get the latest payoll date of the > tables that should have matching in both these tables > > sample o/p should like the below for plan bbb > bbb 2012-0303 2 X > > Regards > Ron
Not offense intended, but this really sounds like a training/school type question. You did not specify if you need to know __the__ single joined row with the maximum payroll date, all rows with __the__ maximum payroll date, or all rows for a given Plan Name value which have the maximum Payroll date for that given Plan Name. What are you matching on? Just the Payroll Date? Just the Plan Name? Or on both the Plan Name and Payroll Date? What output do you want if something doesn't match? For instance, supposed the maximum Payroll date in the second table does not exist in the first, or vice versa? This can happen unless the tables have a RI (Relational Integrity) constraint on them of some sort. Also, which database? I'd assume DB2. But maybe it's Oracle. Or perhaps even CA-Datacom/DB. The SQL has different dialects. Or are you, for some reason, constrained to use only ANSI approved SQL constructs? -- John McKown Systems Engineer IV IT Administrative Services Group HealthMarkets(r) 9151 Boulevard 26 * N. Richland Hills * TX 76010 (817) 255-3225 phone * john.mck...@healthmarkets.com * www.HealthMarkets.com Confidentiality Notice: This e-mail message may contain confidential or proprietary information. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. HealthMarkets(r) is the brand name for products underwritten and issued by the insurance subsidiaries of HealthMarkets, Inc. -The Chesapeake Life Insurance Company(r), Mid-West National Life Insurance Company of TennesseeSM and The MEGA Life and Health Insurance Company.SM ---------------------------------------------------------------------- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@bama.ua.edu with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
