You can get part way there through SQL, though you cannot
(sadly) capture an SQL SELECT to a select list if it contains a GROUP BY
clause.
So here is a kind of work-around using a scrudgy bit of a
program.
I'm sure someone can come up with something better, but
this is what first came to mind :-)
1. Create dict items for ID (@ID right justified)
and MAIN (@ID[".",1,1]).
2. In a program:
TCL = "SELECT MAX(ID) FROM yourfile GROUP BY
MAIN COL.SUP COUNT.SUP;"
Execute TCL, OUT. >
Text
Convert " " To "" In Text ;* remove leading
spaces
Text = [EMAIL PROTECTED], 3, DCount(Text,@FM)-3] ;*
remove leading and trailing blank lines
Open "&SAVEDLISTS&" TO FL
Then
Write Text On FL,
"MyList"
End
CHAIN "GET.LIST MyList"
Hope this helps,
Brian Leach
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Marco Manyevere
Sent: 04 February 2004 08:20
To: [EMAIL PROTECTED]
Subject: Help wanted creating an efficient SELECT statement
Hi All,
I have a file with IDs like this:
1.2, 1.2 1.3, 2.1, 2.2, 2.3, 2.4, 3.1, 4.1, 5.1, 5.2, ...
Each ID is N.X where X is a numeric secondary ID number from 1 going
upwards and N is the primary ID number which may or may not be
numeric. Different primary IDs have a different number of secondary IDs.
For a certain report, I want to select only the records with the highest
numerical value of X for each of N. For the above example, I want to end up with
1.3, 2.4, 3.1, 4.1, 5.2, Can a SELECT statement(s) be composed without
having to write a program? If not what would be the best approach.
Thanks for any help.
Marco
BT Yahoo! Broadband - Free modem offer, sign up online today and save �80
________________________________________________________________________
This email was checked on leaving Microgen for viruses, similar
malicious code and inappropriate content by MessageLabs SkyScan.
DISCLAIMER
This email and any attachments are confidential and may also be
privileged.
If you are not the named recipient, please notify the sender
immediately and do not disclose the contents to any other
person, use it for any purpose, or store or copy the information.
In the event of any technical difficulty with this email, please
contact the sender or [EMAIL PROTECTED]
Microgen Information Management Solutions
http://www.microgen.co.uk
-- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users
