RE: SQL Select Greatest

The CTE approach is probably overkill for a problem this simple, but I find this approach to be much easier to deal with once things get a little more complex...

One thing that was not clear is whether there could be names in filea not in fileb. In my case, I used left outer join to accommodate this.

Hth,
Eric

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jeff Young
Sent: Wednesday, March 18, 2009 3:36 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL Select Greatest

What about 'Select a.id,name,max(number) from filea a join fileb b on a.id = b.id group by a.id'

 
Jeff Young
Sr. Programmer Analyst
IBM -e(logo) server Certified Systems Exper - iSeries Technical Solutions V5R2 
IBM  Certified Specialist- e(logo) server i5Series Technical Solutions Designer V5R3
IBM  Certified Specialist- e(logo)server i5Series Technical Solutions Implementer V5R3
  
 






________________________________
From: "DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Sent: Wednesday, March 18, 2009 3:39:09 PM
Subject: RE: SQL Select Greatest

With B (name, num) as
(    Select fileb.name, max(fileb.num) Group by fileb.name    )

Select A.name, B.num

From filea A left outer join B on (A.name=B.name)

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Michael Ryan
Sent: Wednesday, March 18, 2009 2:02 PM
To: Midrange Systems Technical Discussion
Subject: SQL Select Greatest

So...I have two files...FileA has id and name, FileB has id and somenum.
I
want to retrieve id, name and somenum, where somenum is the highest
value of
somenum for that id. FileA is:
123, Michael
456, Bob
789, Sue

FileB is:
123, 44444
123, 55555
456, 11111
789, 33333
789, 22222

And I want:
123, 55555
456, 11111
789, 33333

How do I select greatest? Max? SELECT a.name, b.somenum FROM
michael/filea
a, michael/fileb b
WHERE a.id = b.id gives me everything of course. A subselect?

Thanks...