Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 3 of 3 total |
Multiple detail records- selecting last, part II |
Sun, Jan 15 2006 10:42 PM | Permanent Link |
Herb (Kraft) | SELECT
digi_client.ClientNumber, digi_client.ClientName, digi_client.Nextfollowup, digi_client.DateAdded, digi_client_status.ClientNumber, digi_client_status.Status, digi_client_status.ChangeNumber FROM digi_client_status INNER JOIN digi_client ON (digi_client_status.ClientNumber = digi_client.ClientNumber) WHERE (digi_client_status.ChangeDate BETWEEN :low and :high) ORDER BY digi_client.DateAdded, digi_client.ClientName, digi_client_status.ChangeNumber DESC top 1 This works, but all I get is one record. When I am trying to do is to get the largest change number for EACH client and return that only Thus (ignoring other fields) Clients- Borland Net Nanny Status- Borland 1/1/06 Contacted Change number 1 Borland 1/15/06 Signed contract Change number 3 Weatherunderground 1/1/06 Contacted Change number 2 Weatherunderground 1/15/06 Change number 4 What I want is: Borland 1/15/06 Weatherunderground 1/15/06 But I am just getting Change number 4 only So I am trying to go through the entire table and then pick the highest status for each master field. Sorry. This is obviously a really dumb question. I may be missing where in the SQL to do it entirely. Herb |
Mon, Jan 16 2006 9:46 AM | Permanent Link |
"Robert" | "Herb (Kraft)" <herb@examprep.us> wrote in message news:9FCAA4B1-6CE1-4C9D-9551-67C3C1788F8E@news.elevatesoft.com... > SELECT > digi_client.ClientNumber, > digi_client.ClientName, > digi_client.Nextfollowup, > digi_client.DateAdded, > digi_client_status.ClientNumber, > digi_client_status.Status, > digi_client_status.ChangeNumber > FROM > digi_client_status > INNER JOIN digi_client ON (digi_client_status.ClientNumber = digi_client.ClientNumber) > WHERE > (digi_client_status.ChangeDate BETWEEN :low and :high) > ORDER BY > digi_client.DateAdded, > digi_client.ClientName, > digi_client_status.ChangeNumber DESC top 1 > > This works, but all I get is one record. > > When I am trying to do is to get the largest change number for EACH client and return that only > > Thus (ignoring other fields) > > Clients- > > Borland > Net Nanny > > Status- > > Borland 1/1/06 Contacted Change number 1 > Borland 1/15/06 Signed contract Change number 3 > Weatherunderground 1/1/06 Contacted Change number 2 > Weatherunderground 1/15/06 Change number 4 > > What I want is: > > Borland 1/15/06 > Weatherunderground 1/15/06 > > But I am just getting Change number 4 only > > So I am trying to go through the entire table and then pick the highest status for each master field. > > Sorry. This is obviously a really dumb question. I may be missing where in the SQL to do it entirely. > > Herb > > > |
Mon, Jan 16 2006 9:49 AM | Permanent Link |
"Robert" | You need to set up a master detail relationship, where the master key is a
parameter INNER JOIN digi_client ON (digi_client_status.ClientNumber = :ClientNumber) and the detail query's mastersource is the datasource for the master table. Then the detail gets refreshed on each change of the master. Robert "Herb (Kraft)" <herb@examprep.us> wrote in message news:9FCAA4B1-6CE1-4C9D-9551-67C3C1788F8E@news.elevatesoft.com... > SELECT > digi_client.ClientNumber, > digi_client.ClientName, > digi_client.Nextfollowup, > digi_client.DateAdded, > digi_client_status.ClientNumber, > digi_client_status.Status, > digi_client_status.ChangeNumber > FROM > digi_client_status > INNER JOIN digi_client ON (digi_client_status.ClientNumber = digi_client.ClientNumber) > WHERE > (digi_client_status.ChangeDate BETWEEN :low and :high) > ORDER BY > digi_client.DateAdded, > digi_client.ClientName, > digi_client_status.ChangeNumber DESC top 1 > > This works, but all I get is one record. > > When I am trying to do is to get the largest change number for EACH client and return that only > > Thus (ignoring other fields) > > Clients- > > Borland > Net Nanny > > Status- > > Borland 1/1/06 Contacted Change number 1 > Borland 1/15/06 Signed contract Change number 3 > Weatherunderground 1/1/06 Contacted Change number 2 > Weatherunderground 1/15/06 Change number 4 > > What I want is: > > Borland 1/15/06 > Weatherunderground 1/15/06 > > But I am just getting Change number 4 only > > So I am trying to go through the entire table and then pick the highest status for each master field. > > Sorry. This is obviously a really dumb question. I may be missing where in the SQL to do it entirely. > > Herb > > > |
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 |