List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:July 30 1999 4:57pm
Subject:DISTINCT keyword not doing as I expect...
View as plain text  
>>>>> "DC" == DC Mahoney <dan@stripped> writes:

DC> I'm sure I'm overlooking something simple, and I hope one 
DC> of you folks can point out to me my errors.

DC> I'm running MySQL 3.21.31 on Solaris 7. My database "ads"
DC> contains tables including "homes".  The structure of this 
DC> table is:

DC> slug       char(12),
DC> class      char(6),
DC> ad_text    text,
DC> sort_text  char(32),
DC> date_added date,
DC> logo       text,
DC> photo      text,
DC> age        smallint

DC> When I do the query:
DC>    SELECT sort_text, slug, ad_text, date_added, log, 
DC>     photo, class, FROM homes WHERE (class = '705R')
DC>     AND age <= 1 ORDER BY class, sort_text, date_added DESC
DC> I get 276 rows returned.  When I modify the query to:    
DC>    SELECT DISTINCT sort_text, slug, ad_text, date_added, log, 
DC>     photo, class, FROM homes WHERE (class = '705R')
DC>     AND age <= 1 ORDER BY class, sort_text, date_added DESC
DC> I still get 276 rows returned, though I have several
DC> (a dozen or so) ads where sort_text is duplicated.

DC> My objective is to get only the most recent occurence
DC> of each ad.

DC> So how come adding the DISTINCT keyword before the 
DC> "sort_text" column name doesn't give me distinct 
DC> rows?

Hi!

DISTINCT didn't work with MySQL 3.21 on BLOB/TEXT columns.

You can on the other hand try using MID(sort_text,1,255) to
on your text columns to check the distinctness of up to 255 characters.

Regards,
Monty
Thread
DISTINCT keyword not doing as I expect...DC Mahoney15 Jul
  • Re: DISTINCT keyword not doing as I expect...Paul DuBois15 Jul
    • Re: DISTINCT keyword not doing as I expect...jkraai15 Jul
  • Optimizing a P133 for MySQLVan15 Jul
    • Re: Optimizing a P133 for MySQLSasha Pachev15 Jul
    • Re: Optimizing a P133 for MySQLJunster15 Jul
      • Re: Optimizing a P133 for MySQLOrlando Andico15 Jul
        • Re: Optimizing a P133 for MySQLJunster15 Jul
          • Re: Optimizing a P133 for MySQLOrlando Andico15 Jul
            • Re: Optimizing a P133 for MySQLEd Carp15 Jul
              • Re: Optimizing a P133 for MySQLOrlando Andico15 Jul
                • Re: Optimizing a P133 for MySQLJon Drukman15 Jul
                  • Re: Optimizing a P133 for MySQLOrlando Andico15 Jul
                • Re: Optimizing a P133 for MySQLChris Trown15 Jul
                  • Re: Optimizing a P133 for MySQLEd Carp15 Jul
                    • Re: Optimizing a P133 for MySQLOrlando Andico16 Jul
                      • Re: Optimizing a P133 for MySQLVan16 Jul
              • Re: Optimizing a P133 for MySQLVan15 Jul
              • Re: Optimizing a P133 for MySQLT├Ánu Samuel16 Jul
                • Re: Optimizing a P133 for MySQLJon Drukman16 Jul
        • Re: Optimizing a P133 for MySQLJon Drukman15 Jul
      • Searching across multiple tablesBrighten Godfrey15 Jul
        • Searching across multiple tablessinisa15 Jul
        • Re: Searching across multiple tablesChristian Mack21 Jul
    • Re: Optimizing a P133 for MySQL {Interim Results}Van16 Jul
      • Re: Optimizing a P133 for MySQL {Interim Results}Ed Carp16 Jul
  • DISTINCT keyword not doing as I expect...Michael Widenius30 Jul
Re: Optimizing a P133 for MySQLDerick H Siddoway16 Jul
Re: Optimizing a P133 for MySQLKen Farwell16 Jul