On Feb 27, 1:09 am, "Dave" <dave_bran...@msn.com> wrote:
> Here is an extremely simplified copy of a table I have:
>
> Field 1 Field 2 Field 3
> 1 4 E
> 1 4 B
> 1 4 H
> 1 5 Q
> 2 9 O
> 2 8 I
> 2 9 U
> 2 8 W
> 3 7 F
> 3 6 C
> 3 7 Q
> 3 7 Z
>
> I would like to filter out the records that have the max value in
> Field 2 within each group of equal values in Field 1.
>
> Here is what the end result should be:
>
> Field 1 Field 2 Field 3
> 1 5 Q
> 2 9 O
> 2 9 U
> 3 7 F
> 3 7 Q
> 3 7 Z
>
> Notice only the records with the higher value in Field 2 display
> within each group of equal values in Field 1.
>
> Any ideas how I can accomplish this in a view using a function, or any
> other simple way of accomplishing this goal?
>
> Thanks you very much in advance for any assistance,
>
> Dave

On Feb 27, 5:21 am, "Jason Lepack" <jlep...@gmail.com> wrote:
> SELECT A.[field 1],
> A.[field 2],
> A.[field 3]
> FROM dbo.Table_1 AS A
> INNER JOIN (SELECT [field 1],
> MAX([field 2]) AS biggy
> FROM dbo.Table_1
> GROUP BY [field 1]) AS B
> ON A.[field 1] = B.[field 1]
> AND A.[field 2] = B.biggy
>
> Cheers,
> Jason Lepack
>
> On Feb 27, 1:09 am, "Dave" <dave_bran...@msn.com> wrote:
>
>
>
> > Here is an extremely simplified copy of a table I have:
>
> > Field 1 Field 2 Field 3
> > 1 4 E
> > 1 4 B
> > 1 4 H
> > 1 5 Q
> > 2 9 O
> > 2 8 I
> > 2 9 U
> > 2 8 W
> > 3 7 F
> > 3 6 C
> > 3 7 Q
> > 3 7 Z
>
> > I would like to filter out the records that have the max value in
> > Field 2 within each group of equal values in Field 1.
>
> > Here is what the end result should be:
>
> > Field 1 Field 2 Field 3
> > 1 5 Q
> > 2 9 O
> > 2 9 U
> > 3 7 F
> > 3 7 Q
> > 3 7 Z
>
> > Notice only the records with the higher value in Field 2 display
> > within each group of equal values in Field 1.
>
> > Any ideas how I can accomplish this in a view using a function, or any
> > other simple way of accomplishing this goal?
>
> > Thanks you very much in advance for any assistance,
>
> > Dave- Hide quoted text -
>
> - Show quoted text -

On Feb 27, 11:13 am, "Dave" <dave_bran...@msn.com> wrote:
> On Feb 27, 5:21 am, "Jason Lepack" <jlep...@gmail.com> wrote:
>
>
>
>
>
> > SELECT A.[field 1],
> > A.[field 2],
> > A.[field 3]
> > FROM dbo.Table_1 AS A
> > INNER JOIN (SELECT [field 1],
> > MAX([field 2]) AS biggy
> > FROM dbo.Table_1
> > GROUP BY [field 1]) AS B
> > ON A.[field 1] = B.[field 1]
> > AND A.[field 2] = B.biggy
>
> > Cheers,
> > Jason Lepack
>
> > On Feb 27, 1:09 am, "Dave" <dave_bran...@msn.com> wrote:
>
> > > Here is an extremely simplified copy of a table I have:
>
> > > Field 1 Field 2 Field 3
> > > 1 4 E
> > > 1 4 B
> > > 1 4 H
> > > 1 5 Q
> > > 2 9 O
> > > 2 8 I
> > > 2 9 U
> > > 2 8 W
> > > 3 7 F
> > > 3 6 C
> > > 3 7 Q
> > > 3 7 Z
>
> > > I would like to filter out the records that have the max value in
> > > Field 2 within each group of equal values in Field 1.
>
> > > Here is what the end result should be:
>
> > > Field 1 Field 2 Field 3
> > > 1 5 Q
> > > 2 9 O
> > > 2 9 U
> > > 3 7 F
> > > 3 7 Q
> > > 3 7 Z
>
> > > Notice only the records with the higher value in Field 2 display
> > > within each group of equal values in Field 1.
>
> > > Any ideas how I can accomplish this in a view using a function, or any
> > > other simple way of accomplishing this goal?
>
> > > Thanks you very much in advance for any assistance,
>

> > > Dave- Hide quoted text -
>
> > - Show quoted text -
>
> Jason...you rock!!! I wasn't familiar with using subqueries in this
> manner, as I'm a bit of a novice. Thank you soooo much for your
> assistance!!!
>
> Cheers,
>
> Dave- Hide quoted text -
>
> - Show quoted text -