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@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@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@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@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@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@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
