Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 2 of 2 total |
Select the (entire) row that contains the MAX column value |
Sat, Feb 13 2010 9:33 PM | Permanent Link |
Tony Pomfrett | Hi,
How can I select the (entire) row which contains the MAX column value? The following query selcts the MAX of Col1 for each value of Col2 but it returns the value of Col3 for the first row of the table. SELECT MAX(Col1), Col2, Col3 from MyTable GROUP BY Col2 What I need is the MAX of Col1 for each value of Col2 as well as the value of Col3 in the row where MAX(Col1) occurs. Any suggestions? Thanks, Tony. |
Sun, Feb 14 2010 3:40 PM | Permanent Link |
"Robert" | "Tony Pomfrett" <tonyp@aline.com.au> wrote in message news:D7CDB367-F8C1-4330-94AE-3954E6E80524@news.elevatesoft.com... > Hi, > > How can I select the (entire) row which contains the MAX column value? > > The following query selcts the MAX of Col1 for each value of Col2 but it > returns the value of Col3 for the first row of the table. > > SELECT MAX(Col1), Col2, Col3 from MyTable > GROUP BY Col2 > > What I need is the MAX of Col1 for each value of Col2 as well as the value > of Col3 in the row where MAX(Col1) occurs. > SELECT MAX(Col1) MyMAX, Col2 MyCol2 INTO MEMORY\TEMP FROM MyTable GROUP BY MyCol2; SELECT [DISTINCT] MyMax, MyCol2, Col3 FROM MEMORY\TEMP JOIN MYTABLE ON ((MyMax = Col1) and (MyCol2 = Col2)); I think so, anyway. Use the distinct if you have multiple matches on max. If the output of the first select is large, add an index to the memory table. Robert |
This web page was last updated on Tuesday, April 30, 2024 at 03:55 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |