Hi Francico, yes that would work. Thanks. The only thing I don't like is created a 200,000 record temporary table.
I may have a different solution, will post it soon. -----Original Message----- From: Francisco Reinaldo [mailto:[EMAIL PROTECTED]] Sent: Wednesday, 10 July 2002 02:22 To: Miles Roper Subject: RE: UDF, Can anyone please help? Hi Miles, Then I think that the easiest way is to create a temporary table from this query: Create Temporary Table TempTable Select Max(IDField) >From TheTable Group by Field1; Then your inner join the temporary table with yours: Select t1.* from TempTable tt, TheTable l1 Where tt.IDField, t1.IDField Finally you drop the temp table: drop table if exists TempTable This could have been achieved in one step with sub-queries but MySql does not support them. I hope it helped. --- Miles Roper <[EMAIL PROTECTED]> wrote: > Hi Frank, > > Good idea, but I actually need the last value, ie > > IDField Field1 Field2 > 1 A 1 > 2 A 4 > 3 A 2 > 4 A 3 > 5 B 7 > 6 B 10 > 7 B 3 > 8 B 8 > 9 C 1 > 10 C 2 > 11 C 4 > 12 C 1 > > The answer would be > > select field1, last(field2) from table1 group by > field1 > > Feild1 Field2 > A 3 > B 8 > C 1 > > Select Field1, Max( Field2 ) From MyTable Group by > Field1 > > Would produce a wrong answer > > Feild1 Field2 > A 4 > B 10 > C 4 > > I'm keeping an eye on the mailing list via the net. > :o) I get to many > emails as it is :o) > > Hope this makes sense :o) Thanks for you help. > > Cheers > > Miles > > -----Original Message----- > From: Francisco Reinaldo [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, 9 July 2002 03:38 > To: Miles Roper; '[EMAIL PROTECTED]' > Subject: Re: UDF, Can anyone please help? > > > Hi Miles, > > Could you explain the same thing with a some sample > data? I does not need to be your data. I think that > what you want to do is this but I am not sure. > > Field1 Field2 > A 1 > A 1 > A 2 > A 3 > B 1 > B 1 > B 2 > C 2 > C 2 > C 4 > C 4 > > When you say last do you mean this in ms access? > Select Field1, Last( Field2 ) From MyTable Group by > Field1 > > Maybe I am not understanding your question, but why > don't you do this? > > Select Field1, Max( Field2 ) From MyTable Group by > Field1 > > Bye and good luck. > > P.S. One tip about the volume of emails. Check them > dialy and delete the ones that you are not > interested > in. If you are not subscribeb to the mail-list, how > are you going to help other people? :-) > > > > --- Miles Roper <[EMAIL PROTECTED]> wrote: > > Hi, > > > > I've being trying for the last couple of months to > > convert a MS Access to > > Mysql. I pull out data extract from around 100 + > > queries. These took about > > 5 months of solid work to develop and are > extremely > > complex. I can convert > > these to mysql, but have one big problem. There > is > > no last function in > > mysql. I know you can do a descending sort by ID > > and grab the first row to > > get the same thing. This will not work as I need > to > > do it over several > > thousand different groups. So I need the last row > > per group. > > > > I have been trying to write a aggregate last UDF > > function in mysql, and > > would consider this to be very simple, however, I > > can't get it to work. I > > don't really know how to program in C and my > > understanding of it is quite > > vague. I really need some help to do this from > > someone in the community. I > > have posted several times in the past and haven't > > heard anything from > > anyone. I am getting quite frustrated and are > about > > ready to flag this > > exercise as a great waste of time. > > > > I have successful so far got the UDF to compile, > and > > installed. I had to > > recompile mysql for HPUx to get it to support UDFs > > in the first place. It > > has taken me 50+ hours to export several hundred > meg > > of data from access to > > mysql. I have even written quite a bit of > > code/scripts to get it to the > > point where I can start fixing things. > > > > So, please, can anyone help me with this. I don't > > expect someone to do this > > all for me, but just a LITTLE help would be nice. > I > > will quite happily post > > this back into the community if I can get it to > > work. > > > > I have removed myself off the mailing digest as > the > > volume is too high. If > > you could please email me at > > [EMAIL PROTECTED] > > > > Yours Gratefully > > > > Miles > > > > > > > --------------------------------------------------------------------- > > Before posting, please check: > > http://www.mysql.com/manual.php (the manual) > > http://lists.mysql.com/ (the list > > archive) > > > > To request this thread, e-mail > > <[EMAIL PROTECTED]> > > To unsubscribe, e-mail > > > <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: > > http://lists.mysql.com/php/unsubscribe.php > > > > > __________________________________________________ > Do You Yahoo!? > Sign up for SBC Yahoo! Dial - First Month Free > http://sbc.yahoo.com > __________________________________________________ Do You Yahoo!? Sign up for SBC Yahoo! Dial - First Month Free http://sbc.yahoo.com --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php