SQL Help

[William Prothero]

Paul:
Thanks, the suggestion you made worked. I used:
put "SELECT * FROM "&theTable&" WHERE "&col1Name&" = "&col1Value&" AND "&col2Name&" = "&" order by iStoreGrp desc limit 1" into theQuery

This returns the row with the specified meterNum and valveNum and the maximum value of iStoreGrp.

Thanks a bunch!
Bill

> On Jun 2, 2018, at 4:46 AM, Paul Dupuis via use-livecode <use-livecode at lists.runrev.com> wrote:
> 
> Sorry, I missed the all columns bit. Yes, LIMIT is the way to do this
> 
> SELECT * FROM valveFlowsA WHERE meterNum =1 AND valveNum =3
> ORDER BY iStoreGrp DESC LIMIT 1
> 
> If you still need it. The DESC insure the record with the highest (max)
> iStoreGrp value is returned first. CAUTION: If the data contained two
> records with meterNum =1 AND valveNum =3 and the exact same iStoreGrp
> value, I don't believe there is a way to predict which record will be
> the first returned. This only works if iStoreGrp is effectively unique
> 
> 
> On 6/1/2018 8:48 PM, Mike Bonner via use-livecode wrote:
>> if I understand correctly, and just off the top of my head, using "limit"
>> is one way to do this...
>> The sql pseudo code would be something like:  select * from yourtable order
>> by iStoreGrp descending where meterNum = 1 AND valveNum = 3 limit 1
>> This would grab all the rows, sort them by istoregrp and return the top
>> entry
>> 
>> On Fri, Jun 1, 2018 at 6:31 PM, William Prothero via use-livecode <
>> use-livecode at lists.runrev.com> wrote:
>> 
>>> Folks:
>>> Maybe someone can answer this easily and save me some hair pulling. I know
>>> it’s google-able, but I’ve tried and know it’s going to be a trudge to get
>>> the right format.
>>> 
>>> I want to make a query to a mySQL db that:
>>> The table is named: “valveFlowsA”
>>> I want the query to return the row with the maximum of a column named
>>> “iStoreGrp” when
>>> columns named “meterNum” and “valveNum” are specified. So, with meterNum
>>> =1 and
>>> valveNum =3, I would get the row for the maximum value of “iStoreGrp”.
>>> 
>>> All of these columns are integers.
>>> 
>>> If anybody has this query in their quiver of tools, please let me know.
>>> Thanks!
>>> 
>>> Bill
>>> 
>>> William A. Prothero
>>> http://earthlearningsolutions.org
>>> 
>>> _______________________________________________
>>> use-livecode mailing list
>>> use-livecode at lists.runrev.com
>>> Please visit this url to subscribe, unsubscribe and manage your
>>> subscription preferences:
>>> http://lists.runrev.com/mailman/listinfo/use-livecode
>> _______________________________________________
>> use-livecode mailing list
>> use-livecode at lists.runrev.com
>> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
> 
> 
> 
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode