Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 5 of 5 total |
Getting the Most Recent Date |
Wed, Jan 17 2007 11:27 AM | Permanent Link |
I have a query I need to generate in which one of my tables contains
multiple records for an individual with the same values except for the date the record was entered. I want to retrieve the most recent date and its associated balance for each individual to populate a grid in D7. Here is the query I am currently using: SELECT p.ID, p.Last_Name, p.First_Name, a.AR_Balance, max(ARDate) FROM Patient p INNER JOIN AcctMem am ON am.PatientID = p.ID INNER JOIN AcctBal a ON a.AcctID - am.AcctID GROUP BY p.ID Am I on the right track? Thanks, in advance. | |
Wed, Jan 17 2007 1:25 PM | Permanent Link |
"Robert" | <KChief101@hotmail.com> wrote in message newsisq2l9fftrc9bbtou9rkrn9grridd8q4@4ax.com... >I have a query I need to generate in which one of my tables contains > multiple records for an individual with the same values except for the > date the record was entered. I want to retrieve the most recent date > and its associated balance for each individual to populate a grid in > D7. > > Here is the query I am currently using: > > SELECT p.ID, p.Last_Name, p.First_Name, a.AR_Balance, max(ARDate) > FROM Patient p INNER JOIN AcctMem am ON am.PatientID = p.ID > INNER JOIN AcctBal a ON a.AcctID - am.AcctID > GROUP BY p.ID > > Am I on the right track? Thanks, in advance. Which table is ARDate coming from? Robert |
Wed, Jan 17 2007 8:39 PM | Permanent Link |
On Wed, 17 Jan 2007 13:19:11 -0500, "Robert"
<ngsemail2005withoutthis@yahoo.com.ar> wrote: > ><KChief101@hotmail.com> wrote in message >newsisq2l9fftrc9bbtou9rkrn9grridd8q4@4ax.com... >>I have a query I need to generate in which one of my tables contains >> multiple records for an individual with the same values except for the >> date the record was entered. I want to retrieve the most recent date >> and its associated balance for each individual to populate a grid in >> D7. >> >> Here is the query I am currently using: >> >> SELECT p.ID, p.Last_Name, p.First_Name, a.AR_Balance, max(ARDate) >> FROM Patient p INNER JOIN AcctMem am ON am.PatientID = p.ID >> INNER JOIN AcctBal a ON a.AcctID - am.AcctID >> GROUP BY p.ID >> >> Am I on the right track? Thanks, in advance. > >Which table is ARDate coming from? > >Robert > Sorry. It should be max(a.ARDate). | |
Wed, Jan 17 2007 9:07 PM | Permanent Link |
"Robert" | <KChief101@hotmail.com> wrote in message news:ihjtq2d4vkf2nk8ligp92s83lr0jt34pco@4ax.com... > On Wed, 17 Jan 2007 13:19:11 -0500, "Robert" > <ngsemail2005withoutthis@yahoo.com.ar> wrote: > >> >><KChief101@hotmail.com> wrote in message >>newsisq2l9fftrc9bbtou9rkrn9grridd8q4@4ax.com... >>>I have a query I need to generate in which one of my tables contains >>> multiple records for an individual with the same values except for the >>> date the record was entered. I want to retrieve the most recent date >>> and its associated balance for each individual to populate a grid in >>> D7. >>> >>> Here is the query I am currently using: >>> >>> SELECT p.ID, p.Last_Name, p.First_Name, a.AR_Balance, max(ARDate) >>> FROM Patient p INNER JOIN AcctMem am ON am.PatientID = p.ID >>> INNER JOIN AcctBal a ON a.AcctID - am.AcctID >>> GROUP BY p.ID >>> >>> Am I on the right track? Thanks, in advance. >> >>Which table is ARDate coming from? >> >>Robert >> > Sorry. It should be max(a.ARDate). I think it is a very bad idea to select anything other than the aggregate fields and the fields that you would use in a GROUP BY. Look at this query State Name Balance AR Robert 100 MA John 200 AR Jim 300 SELECT State, Name, sum(balance) from table group by state Which name do you want for AR? SELECT Max(ARDate) MDate, AcctID MAcct into memory\temp from AcctBal group by 2; SELECT p.ID, p.Last_Name, p.First_Name, a.AR_Balance, m.Mdate FROM Patient p INNER JOIN AcctMem am ON am.PatientID = p.ID INNER JOIN AcctBal a ON a.AcctID = am.AcctID INNER JOIN memory\temp m ON m.MAcct = am.AcctID where m.mdate = a.ARDate; eliminates the ambiguity. Robert |
Thu, Jan 18 2007 5:41 AM | Permanent Link |
"Malcolm" | Agreed.
A couple of years back I asked a similar question and Tim said that it was not *guaranteed* that the column values were from the record with the MAX() value .. and in my case there was no chance of duplicate MAX value records. In my tests I got the correct values but I guess it depends on the complexity of the query and data. Malcolm |
This web page was last updated on Monday, June 17, 2024 at 07:11 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |