List:General Discussion« Previous MessageNext Message »
From:Chris Tucker Date:October 22 2003 5:40pm
Subject:Re: 2 Query with same criteria giving different number of rows
View as plain text  
You're getting a distinct on just the prod_num in the first query, but 
are requesting distinct prod_num, description, line, and content in the 
second one: if any of those last three columns are different between 
rows, you will see ones additional to the count you get in the first 
query.  You either need to count the same set of distinct columns in the 
first query or GROUP BY prod_num in the second.

http://www.mysql.com/doc/en/SELECT.html
http://www.mysql.com/doc/en/GROUP-BY-Functions.html

Chris

Hector Del Curto wrote:
> I have this 2 queries:
> 
> SELECT COUNT(DISTINCT(LEFT(p.prod_num,5))) FROM products p, tlines l,
> prod_line pl WHERE l.Line = 'Solaris' AND l.id=pl.line_id AND pl.prod_id =
> p.id_product
> 
> SELECT DISTINCT(LEFT(p.prod_num,5))as prod_num, p.description AS
> description,l.Line as line,l.Content as content FROM products p, tlines l,
> prod_line pl WHERE l.Line = 'Solaris' AND l.id=pl.line_id AND pl.prod_id =
> p.id_product
> 
> The only difference between them is that in one I want the count of rows and
> on the other I want the columns.
> When I run the queries I get for example 24 as result of the count and on
> the other query I get 26 records.
> In those 26 records I get 2 prod_num duplicated when I'm asking for
> DISTINCT.
> Now, the other thing is if I only leave the prod_num column the number of
> rows are the same, if I add another column it changes to 26.
> I know I have some duplicates on the second table, and that's affecting the
> results, but I don't know why.
> Any hint on why is that happening?
> 
> 
> 
> 													Thanks,
> 														Hector
> 
> 


Thread
MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Heikki Tuuri22 Oct
  • Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Chris Nolan22 Oct
  • 2 Query with same criteria giving different number of rowsHector Del Curto22 Oct
    • Re: 2 Query with same criteria giving different number of rowsChris Tucker22 Oct
    • Re: 2 Query with same criteria giving different number of rowsIllyes Laszlo23 Oct
  • Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Eduardo D Piovesam23 Oct
  • Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Eduardo D Piovesam23 Oct
Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Heikki Tuuri24 Oct
Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Heikki Tuuri24 Oct
  • Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Martijn Tonies24 Oct
    • Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Chris Nolan24 Oct
  • Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Eduardo D Piovesam24 Oct
    • MySQL/InnoDB-4.0.16 +Optimizer behaviourSergey S. Kostyliov29 Oct
  • Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Heikki Tuuri24 Oct
  • Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Eduardo D Piovesam24 Oct
  • Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Martijn Tonies24 Oct
  • Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Heikki Tuuri27 Oct
Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Heikki Tuuri26 Oct
Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Martijn Tonies26 Oct
Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Jon Hancock27 Oct
  • Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1Chris Nolan27 Oct
    • InnoDB on Raw partitions in OSX (was Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1)Gabriel Ricard27 Oct
  • Partial replicate InnoDB -> MyISAMJon Hancock28 Oct
    • Re: Partial replicate InnoDB -> MyISAMChris Nolan28 Oct
  • Re: Partial replicate InnoDB -> MyISAMJon Hancock28 Oct
Re: InnoDB on Raw partitions in OSX (was Re: MySQL/InnoDB-4.0.16 is released + sneak peek of 4.1.1)Heikki Tuuri27 Oct