Re: SQL Select Greatest

[Michael Ryan <michaelrtr@xxxxxxxxx>]

Thanks Rob...

On Wed, Mar 18, 2009 at 3:21 PM, <rob@xxxxxxxxx> wrote:

> select filea.id, filea.name, max(fileb.somenum)
> from filea join fileb on filea.id=fileb.id
> group by filea.id, filea.name
>
> Rob Berendt
> --
> Group Dekko Services, LLC
> Dept 01.073
> Dock 108
> 6928N 400E
> Kendallville, IN 46755
> http://www.dekko.com
>
>
>
>
>
> From:
> Michael Ryan <michaelrtr@xxxxxxxxx>
> To:
> Midrange Systems Technical Discussion <MIDRANGE-l@xxxxxxxxxxxx>
> Date:
> 03/18/2009 03:05 PM
> Subject:
> SQL Select Greatest
> Sent by:
> midrange-l-bounces@xxxxxxxxxxxx
>
>
>
> 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...
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.