"Harish Gabbita" <[EMAIL PROTECTED]> wrote on 06/27/2005 10:17:48 AM:
> Hi Everybody, > I am using MySql 4.0.21 standard version. I have a profile table with the > structure in similar manner: > mysql> select A0,A1,A14,A15,A19,Split from PROFILE where A1=100; > +-------+-----+-----+-----+-----+------+ > | A0 | A1 | A14 | A15 | A19 | Split| > +-------+-----+-----+-----+-----+------+ > | 10005 | 100 | 0 | 0 | 1 | OLD | > | 10006 | 100 | 0 | 0 | 1 | OLD | > | 20005 | 100 | 0 | 0 | 1 | OLD | > | 30005 | 100 | 0 | 0 | 1 | OLD | > +-------+-----+-----+-----+-----+------+ > > In this table, I would like to get a single row for value A1=100 and > A14=0,A15=0 and A19=1. I am currently using Limit 1 in the select query. > Note: My data is not based on A0 data. > If the table runs into millions of rows, is it a good suggestion to use > Limit. Or Is there any alternative? > > Thanks, > Harish Two options: Don't ask for the A0 or Split columns and use DISTINCT. Wrap A0 and split columns with one of the aggregate functions and use GROUP BY. SELECT DISTINCT A1,A14,A15,A19 FROM PROFILE where A1=100; -- or -- SELECT MIN(A0),A1,A14,A15,A19,MAX(Split) FROM PROFILE where A1=100 GROUP BY A1,A14,A15,A19; -- an alternative GROUP BY declaration -- using just the columns' positions SELECT MIN(A0),A1,A14,A15,A19,MAX(Split) FROM PROFILE where A1=100 GROUP BY 2,3,4,5; Shawn Green Database Administrator Unimin Corporation - Spruce Pine
