RE: Need help with SQL query

["Chris Payne" <CPayne@xxxxxxxxxxxxxxx>]

Ok,
        Even if the data is different, I think we can come up with
something similar, combine the two fields you are interested in, using
functions like(Days(),char(),integer() and so on) so that the primary
one is out front and the secondary one is behind it, make sure that they
do not overlap at all, then take the max() of that composite  field.
This solution might have performance problems but it should get the
record that you are interested in.

Chris

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of John Candidi
Sent: Friday, December 08, 2006 10:34 AM
To: 'Midrange Systems Technical Discussion'
Subject: RE: Need help with SQL query

Unfortunately this isn't real data.  The real data is dates and strings.
I
just used a simple data example so as not to get caught up on the data
itself.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Chris Payne
Sent: Wednesday, December 06, 2006 4:45 PM
To: Midrange Systems Technical Discussion
Subject: RE: Need help with SQL query

If you just want the highest age if we have a tie on salary you could
do:

With inlineview1 as (
select 
 Name,
 Max(Salary + age/100) as salary_and_age
From
 Sometable
With
 Someconditions
Group by
 name
)
Select a.* 
From
 Sometable a join
 Inlinebiew1 b on
  a.name = b.name and
  a.salary = integer(b.salary_and_age) and
  a.age = mod(b.salary_and_age,1)*100

assuming your salaries come out to even dollars. If you pay someone
65,000.23 per year you will have to increase, the number you divide age
by until it is out past the cents if you want to be real safe you could
divide by a much larger number.
  

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Chris Payne
Sent: Wednesday, December 06, 2006 4:35 PM
To: Midrange Systems Technical Discussion
Subject: RE: Need help with SQL query

Lets say you have 

John     Smith    25         60000

John     Smith    40         20000

John     Smith    20         15000

As the only contents of your file, which record do you want back? If we
get the John Smith with the highest salary, or the John Smith with the
highest age we will be down to one John Smith and it will be meaningless
to narrow it down further.

Chris

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of John Candidi
Sent: Wednesday, December 06, 2006 4:00 PM
To: 'Midrange Systems Technical Discussion'
Subject: Need help with SQL query

I am trying to figure out a query that needs to display rows of grouped
data
based on max values of two columns.

 

Here is an example.

 

Fname  lname   age       salary

----------  ---------   ----        --------

John     Smith    39         20000

John     Smith    38         20000

John     Smith    40         15000

Adam    Apple    20         25000

Adam    Apple    21         25000

Adam    Apple    19         18000

 

I need to return one line from each group (one john smith and one Adam
Apple) based on highest salary and age.  For john Smith, highest Salary
is
20000 and then highest age is 39, so the row returned would be John
Smith 39
20000.  For Adam Apple it would be Adam Apple 21 25000.

 

I can get it narrowed down to rows grouped with highest salary, but then
I
can not think how to get it to narrow it down another level and only
display
the one with the larger age number after that.

 

 

John A Candidi

Rutgers Insurance Companies

IT Director - Iseries Manager

856-779-2274