Results 1 to 9 of 9

Thread: Selct distinct returns incorrect results in SQL 2000

  1. #1
    Join Date
    Nov 2002
    Location
    Glen Mills
    Posts
    5

    Question Selct distinct returns incorrect results in SQL 2000

    Hi, I am seeking desperate help.
    We recently upgraded our SQL server from 7.0 TO 2000. Our user are using Bussines objects tools for the reports. When they use the following query " Select distinct (snapshotdate) from tablename group by snapshotdate" returns incorrect results in the meaning of the dates are missing. Whe we use different set of query " that is by using snapshotdate >= date " fundtion it returns correct information. Data is there. Why am I getting this discripence.

    I ran update statistics, rebuild indexes. Optimization and integrity everything performed.
    SQL 2000 service pack 2 is also applied to the server and client.

    All suggestion are welcome. Seeking help sincierly.

    Thanks,
    Ramakrishna
    Last edited by rearasi; 11-06-2002 at 07:54 AM.

  2. #2
    Join Date
    Oct 2002
    Posts
    42
    So what are the two different queries that you are running? What data is it that is "missing" from the distinct query? Why do you use the group by?

  3. #3
    Join Date
    Nov 2002
    Location
    Glen Mills
    Posts
    5

    Question select distinct returns incorrect results in sql 2000

    In bussiness objects reports tools use distinct function to get the dates.
    Here is the query which is giving incorrect results.

    "select distinct (snapshotdate) from tablename"

    The results from the above query------9/29/2002 0:00
    9/30/2002 0:00
    10/3/2002 0:00
    10/4/2002 0:00
    10/7/2002 0:00
    10/8/2002 0:00
    10/11/2002 0:00
    10/12/2002 0:00
    10/16/2002 0:00
    10/17/2002 0:00
    10/20/2002 0:00
    10/21/2002 0:00
    10/24/2002 0:00
    10/25/2002 0:00
    10/29/2002 0:00
    10/30/2002 0:00
    11/2/2002 0:00
    11/3/2002 0:00 Please note that some of the dates are missing the query.

    The second set of query is ------
    "select SnapshotDate
    from tabelname
    where snapshotdate >= '10/25/2002 00:00:00'
    group by snapshotdate"

    The results from the above query is----
    10/25/2002 0:00
    10/26/2002 0:00
    10/27/2002 0:00
    10/28/2002 0:00
    10/29/2002 0:00
    10/30/2002 0:00
    10/31/2002 0:00
    11/1/2002 0:00
    11/2/2002 0:00
    11/3/2002 0:00
    11/4/2002 0:00
    11/5/2002 0:00 please note that results with all the dates.

    Please, kindly help me to resolve this unexpalinable thing.

    Thanks,

  4. #4
    Join Date
    Oct 2002
    Posts
    42
    I can't explain that either. I've not used business objects. Do you get the same results if you run the queries in query analyzer?

  5. #5
    Join Date
    Nov 2002
    Location
    Glen Mills
    Posts
    5
    Hi,
    Yes, I get the same results on query analyzer. I am not sure what is wrong. I am working on this for past few days and no luck.

    I can certainly use any ideas or recomendations with this. I tried everything I can think of.

    Need HELP....

    Thanks,
    Ramakrishna Earasi

  6. #6
    Join Date
    Oct 2002
    Posts
    42
    The only thing I can think of here is that the distinct comparison is rounding the dates to the nearest day, and therefore that is why you are loosing days that are consecutive.

    For example it could be that you have an order on 10/25 and 10/26 but so far as the distinct clause is concerned this could be the same distinct day?

    It might be worth testing it with a set of dates in a temporary table.

  7. #7
    Join Date
    Nov 2002
    Location
    Glen Mills
    Posts
    5
    The same set of queries worked fine on SQL 7.0. This is happening after we upgraded sql server to 2000.

  8. #8
    Join Date
    Oct 2002
    Posts
    42
    That's most bizarre. If you have space / time I would consider copying data into a different database to rule out database corruption.

  9. #9
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    try this
    select distinct convert(varchar(10),snapshotdate,101) from tablename

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •