Grig, Sorry, I misunderstood what you really wanted. Of Course Mike's solution works fine and is a method I've not used for ages and ages as I must admit I normally would resort to a two pass technique. Old habits die hard I'm afraid. Well done Mike
Dave Crozier -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Grigore Dolghin Sent: 02 March 2008 16:25 To: profox@leafe.com Subject: Re: SQL Select help needed Hi, Dave and thanks for answering. Problem is it doesn't work right. I have changed the values a little bit to make it more obvious. Using your set of data I would like to get a cursor that has 5 records, each record contains one car model and next to it the most recent registration number. Please replace my code in your code and run the thing. You'll notice that the registration numbers are all listed, no matter if they're old or new, pushing the other ones away from list. Any ideas? ======================= Select 0 Create Cursor car (Id I, Serial C(10), Model C(20)) Insert into car values (1, "Ford", "Ford") Insert into car values (2, "Vauxhall", "Vauxhall") Insert into car values (3, "Austin", "Austin") Insert into car values (4, "BMW", "BMW") Insert into car values (5, "Mercedes", "Mercedes") Select 0 Create Cursor Reg (Id I, CarId I, RegNumber C(25), Regdate D) Insert into Reg values (1, 1, "FordThisOne", {^2004/01/01}) Insert into Reg values (2, 1, "FordNotGood", {^1994/01/01}) Insert into Reg values (3, 2, "VauxhallThisOne", {^2004/02/01}) Insert into Reg values (4, 3, "AustinNotGood", {^2004/03/01}) Insert into Reg values (5, 3, "AustinThisOne", {^2005/01/02}) Insert into Reg values (6, 4, "BMWThisOne", {^2004/01/01}) Insert into Reg values (7, 5, "MercedesThisOne", {^2004/05/01}) ===================== ----- Original Message ----- From: "Dave Crozier" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Sunday, March 02, 2008 5:39 PM Subject: RE: SQL Select help needed > Close tables all > Set date british > Set century off > Grig, > > Select 0 > Create Cursor car (Id I, Serial C(10), Model C(20)) > Insert into car values (1, "A01", "Ford") > Insert into car values (2, "A02", "Vauxhall") > Insert into car values (3, "A03", "Austin") > Insert into car values (4, "A04", "BMW") > Insert into car values (5, "A05", "Mercades") > > Select 0 > Create Cursor Reg (Id I, CarId I, RegNumber C(10), Regdate D) > Insert into Reg values (1, 1, "A1029", {^2004/01/01}) > Insert into Reg values (1, 1, "A1029", {^1994/01/01}) > Insert into Reg values (1, 2, "C1029", {^2004/02/01}) > Insert into Reg values (1, 3, "C1029", {^2004/03/01}) > Insert into Reg values (1, 3, "C1029", {^2005/01/02}) > Insert into Reg values (1, 4, "D1029", {^2004/01/01}) > Insert into Reg values (1, 5, "E1029", {^2004/05/01}) > > Select all ; > Car.Id as CarId, ; > Car.Serial as Serial, ; > Reg.RegNumber as RegNumber, ; > Max(Reg.RegDate) as Date ; >>From Car ; > Join Reg on Reg.Id=Car.Id ; > where .T. ; > Group by Car.Id, Car.Serial, Reg.Regnumber ; > order by Car.Id, Car.Serial, Reg.Regnumber > > Dave Crozier > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf > Of Grigore Dolghin > Sent: 02 March 2008 15:18 > To: [EMAIL PROTECTED] > Subject: SQL Select help needed > > It seems I am missing something obvious, however I can't figure out what. > > I have two tables, 1-to-many related. Parent table contains car > information, > child table contains the registration numbers, with their date, as they > change. > > Parent table fields: > > Id, Serial, Model > > Child table fields: > > Id, CarId, RegNumber, RegDate > > > How the heck the SQL that returns a list of the cars with their most > recent > registration number looks like? I just can't figure it. > > --- StripMime Report -- processed MIME parts --- > multipart/alternative > text/plain (text body -- kept) > text/html > --- > [excessive quoting removed by server] _______________________________________________ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
