List:General Discussion« Previous MessageNext Message »
From:SGreen Date:June 27 2005 2:32pm
Subject:Re: Enhance a query
View as plain text  
"Harish Gabbita" <hgabbita@stripped> 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
Thread
LEFT JOIN?Angelo Zanetti8 Jun
  • Re: LEFT JOIN?SGreen8 Jun
    • Re: LEFT JOIN?Angelo Zanetti8 Jun
      • Re: LEFT JOIN?Michael Stassen8 Jun
        • Re: LEFT JOIN?Angelo Zanetti8 Jun
          • Re: LEFT JOIN?SGreen8 Jun
  • Re: LEFT JOIN?Alec.Cawley8 Jun
Re: LEFT JOIN?Michael Stassen8 Jun
  • Re: [SOLVED]LEFT JOIN?Angelo Zanetti9 Jun
    • Enhance a queryHarish Gabbita27 Jun
      • Re: Enhance a querySGreen27 Jun