Visual FoxPro SQL Queries - how to get a field associated with the maximum of another ?

Author

how to get a field associated with the maximum of another ?

Pablo De la Puente

2006-01-10, 8:25 pm

Hello friends,

I have the following table: mytable (itemID, field1, field2) where there
could be several records for the same itemID but only one per itemID-field1
combination.

I need to select, for each itemID, the record where field1 has the maximum
value.

Say the table has the following records:
..item1, 15, 34
..item2, 1, 100
..item1, 10, 90
..item 2, 2, 101

I need to select:
..item1, 15, 34
..item2, 2, 101

I know how to get the maximum value of field1 but then, how do I get the
associated field2 ?

Thanks in advance.

Cindy Winegarden

2006-01-11, 3:24 am

Hi Pablo,

Create Cursor Test (ItemID C(10), Field1 I, Field2 I)
Insert Into Test Values ("Item1", 15, 34)
Insert Into Test Values ("Item2", 1, 100)
Insert Into Test Values ("Item1", 10, 90)
Insert Into Test Values ("Item2", 2, 101)

Select T1.ItemID, T1. Field1, T1.Field2 ;
From Test T1 ;
Where T1.Field1 = ;
(Select Max(T2.Field1) ;
From Test T2 ;
Where T2.ItemID = T1.ItemID)

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@msn
.com www.cindywinegarden.com

"Pablo De la Puente" <pdelap@hotmail.com> wrote in message
news:u8loeuhFGHA.2696@TK2MSFTNGP14.phx.gbl...
> Hello friends,
>
> I have the following table: mytable (itemID, field1, field2) where there
> could be several records for the same itemID but only one per
> itemID-field1
> combination.
>
> I need to select, for each itemID, the record where field1 has the maximum
> value.
>
> Say the table has the following records:
> .item1, 15, 34
> .item2, 1, 100
> .item1, 10, 90
> .item 2, 2, 101
>
> I need to select:
> .item1, 15, 34
> .item2, 2, 101
>
> I know how to get the maximum value of field1 but then, how do I get the
> associated field2 ?
>
> Thanks in advance.
>
>

Sponsored Links