SQL 2000 Consistency Issues !! Resultset differences ??

• sbawa

  Grasshopper

  Points: 10

  More actions

  April 16, 2004 at 11:30 am

  #108130

  I have a complex correlated query with multiple nested subqueries.  When running the query with a distinct clause, I get 8000 results:

  QUERY 1:  << 8000 results >>

  select distinct  colA from (select distinct vw1.col1 as colA from vw1 join vw2 where vw3.col1 = vw1.col3)  v

   

  However, without the distinct clause, I get 2000 results:

  QUERY 2:   << 2000 results >>

  select  colA from (select  vw1.col1 as colA from vw1 join vw2 where vw3.col1 = vw1.col3)  v

  Why would this be happening? A query with a distinct clause should always return same or less number of records compared to a query without the distinct clause. 

  I have not yet verified if this issue existed before installation of SQL2000 SP3. Please let me know if anyone else has encountered similar issues with complex (ie poorly written) queries.

  Thanks

   

• Kenneth Wilhelmsson

  SSC-Dedicated

  Points: 30043

  More actions

  April 19, 2004 at 9:18 am

  #503360

  Try to provide the schemas and some sample data along with the actual query that repros this behaviour.

  (names may be changed to protect the innocents

  It's very hard to speculate without something to play with.

  /Kenneth