Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 7 of 7 total |
Grouping by most recent date |
Wed, Jan 18 2006 7:12 PM | Permanent Link |
Mark Shapiro | In the following query, the Child table can hold one or more records for an individual
with the same ssn value but a different intake_date value. Is there a way to ensure that the selected records always have the most recent intake_date value? SELECT MAX(child_id) as child_id, MAX(race) as race, MAX(hispanic) as hispanic, MAX(client_age) as client_age INTO MEMORY\Temp FROM Child GROUP BY ssn; The results are then used in a subsequent SELECT to count individual totals. Mark Shapiro |
Thu, Jan 19 2006 4:05 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Mark,
<< In the following query, the Child table can hold one or more records for an individual with the same ssn value but a different intake_date value. Is there a way to ensure that the selected records always have the most recent intake_date value? >> You're missing the SSN column in the query, for starters (DBISAM requires that the GROUP BY columns exist in the SELECT column list). The desired query would look like this: SELECT ssn, MAX(instake_date) AS intake_date, MAX(child_id) as child_id, MAX(race) as race, MAX(hispanic) as hispanic, MAX(client_age) as client_age INTO MEMORY\Temp FROM Child GROUP BY ssn; -- Tim Young Elevate Software www.elevatesoft.com |
Fri, Jan 20 2006 11:53 AM | Permanent Link |
Mark Shapiro | Thanks Tim. Should I assume by the lack of responses that there is no way to group by SSN
and ensure that the record with the most recent intake_date value is selected? Mark Shapiro |
Fri, Jan 20 2006 12:11 PM | Permanent Link |
"Robert" | "Mark Shapiro" <infocus@swbell.net> wrote in message news:C1171A5E-6211-432F-98BF-B8FDC6112F46@news.elevatesoft.com... > In the following query, the Child table can hold one or more records for an individual > with the same ssn value but a different intake_date value. Is there a way to ensure that > the selected records always have the most recent intake_date value? > > SELECT MAX(child_id) as child_id, MAX(race) as race, MAX(hispanic) as hispanic, > MAX(client_age) as client_age INTO MEMORY\Temp FROM Child > GROUP BY ssn; > > The results are then used in a subsequent SELECT to count individual totals. > This is a very odd query. Why MAX(race), for example? One assumes that each individual will be of one race. MAX(age)? Anyway, in answer to your question, you need to select ssn if you want to GROUP BY ssn, and then select max(intake_date). But I think we are not communicating, it would be better if you explain in more detail what you are trying to accomplish. Robert |
Fri, Jan 20 2006 4:18 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Mark,
<< Thanks Tim. Should I assume by the lack of responses that there is no way to group by SSN and ensure that the record with the most recent intake_date value is selected? >> Umm, does my response count ? -- Tim Young Elevate Software www.elevatesoft.com |
Fri, Jan 20 2006 5:47 PM | Permanent Link |
Mark Shapiro | Okay, let me try this a different way.
Query 1 SELECT child_id, race, hispanic, client_age, ssn, intake_date FROM Child GROUP BY ssn This query displays one record for each unique ssn value but does not necessarily display the record with the most recent intake_date. Query 2 SELECT child_id, race, hispanic, client_age, ssn, intake_date FROM Child GROUP BY ssn, intake_date This query will display multiple records for clients with the same ssn but different intake_date values. Is there a way to group the table in such a way that the result set always consists of unique ssn values and also the most recent intake_date for that ssn? Mark Shapiro |
Fri, Jan 20 2006 7:01 PM | Permanent Link |
"Robert" | "Mark Shapiro" <infocus@swbell.net> wrote in message news:8F4BA578-C93F-45FF-9B1D-F56D4D4D603B@news.elevatesoft.com... > Okay, let me try this a different way. > > Query 1 > > SELECT child_id, race, hispanic, client_age, ssn, intake_date FROM Child > GROUP BY ssn > > This query displays one record for each unique ssn value but does not necessarily display > the record with the most recent intake_date. > > Query 2 > > SELECT child_id, race, hispanic, client_age, ssn, intake_date FROM Child > GROUP BY ssn, intake_date > > This query will display multiple records for clients with the same ssn but different > intake_date values. > > Is there a way to group the table in such a way that the result set always consists of > unique ssn values and also the most recent intake_date for that ssn? > Should have said that before. select ssn tssn, max(intake_date) thidate into memory\temp from child group by ssn; select yourfields from child join memory\temp on (ssn = tssn) and (thidate = intake_date); You will get "yourfields" all from the same record, the one that matches the memory table on both ssn and intake_date. Of course, if you have more than one identical intake_date with the same ssn, you will get multiple records extracted, and this logic does not work. Robert |
This web page was last updated on Saturday, May 4, 2024 at 12:54 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |