Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2005
    Posts
    18

    Unanswered: SQL query Time Stamp/Distinct conflict

    In my following SQL query, I want to display records order by Date and Time. But it is conflicting with DISTINCT clause at the begining. When I remove TimeIssue from Order By, the query runs fine. I also want to know how to optimize the performance of this query.

    SELECT Distinct TmainEntry.DateIssue, TmainEntry.IndemNo, TmainEntry.PermitNo, TVisitorComposition.A12I, TVisitorComposition.C12I, TVisitorComposition.C5I, TVisitorComposition.A12F, TVisitorComposition.C12F, TVisitorComposition.C5F, TMainEntry.TotalVisitors, TMainEntry.NumVCam, TMainEntry.BookingType, TFees.EntryFee, TFees.VehicleFee, TFees.GuideFee, TFees.CameraFee, TFees.TotalFee, TMainEntry.ServiceCode
    FROM TMainEntry, TVisitorComposition, TEntryDates, RoundsInfo, TFees
    WHERE TMainEntry.PermitNo=TVisitorComposition.PermitNo and TMainEntry.PermitNo=RoundsInfo.PermitNo and TMainEntry.PermitNo=TFees.PermitNo and TmainEntry.DateIssue between #01-sep-2006# and #30-nov-2006# and TMainEntry.PermitStatus<>'C'
    ORDER BY TMainEntry.DateIssue, TMainEntry.TimeIssue;

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    perhaps you misunderstand the purpose of the DISTINCT keyword

    it is not possible for the ORDER BY to have a "conflict" with DISTINCT

    furthermore, you should really have only one column for date and time, not two
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2005
    Posts
    18
    In case I need to store them separately.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ah, i see

    well, good luck to you then

    please be advised that the date column will also have a time component, but it will be set to midnight, and the time column will also have a date component, but it will be set to 1899-12-30

    that's the only way you can store them separately

    well, okay, there's another way, using TEXT columns, but shurley you don't want to do that, eh
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    who is Shirley?

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by rpk2006
    SELECT Distinct TmainEntry.DateIssue, TmainEntry.IndemNo, TmainEntry.PermitNo, TVisitorComposition.A12I, TVisitorComposition.C12I, TVisitorComposition.C5I, TVisitorComposition.A12F, TVisitorComposition.C12F, TVisitorComposition.C5F, TMainEntry.TotalVisitors, TMainEntry.NumVCam, TMainEntry.BookingType, TFees.EntryFee, TFees.VehicleFee, TFees.GuideFee, TFees.CameraFee, TFees.TotalFee, TMainEntry.ServiceCode
    FROM TMainEntry, TVisitorComposition, TEntryDates, RoundsInfo, TFees
    WHERE TMainEntry.PermitNo=TVisitorComposition.PermitNo
    and TMainEntry.PermitNo=RoundsInfo.PermitNo
    and TMainEntry.PermitNo=TFees.PermitNo
    and TmainEntry.DateIssue between #01-sep-2006# and #30-nov-2006#
    and TMainEntry.PermitStatus<>'C'
    ORDER BY TMainEntry.DateIssue, TMainEntry.TimeIssue;
    With respect to optimization:
    If the "PermitStatus" column has only a fixed set of possible values, it will typically be more efficient (depending on the RDBMS and on the presence of indices) to replace the last condition by e.g.
    Code:
    TMainEntry.PermitStatus IN ('A','B')
    where ('A','B') is to be replaced by the set of all values (except 'C').
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by rpk2006
    SELECT Distinct TmainEntry.DateIssue, TmainEntry.IndemNo, TmainEntry.PermitNo, TVisitorComposition.A12I, TVisitorComposition.C12I, TVisitorComposition.C5I, TVisitorComposition.A12F, TVisitorComposition.C12F, TVisitorComposition.C5F, TMainEntry.TotalVisitors, TMainEntry.NumVCam, TMainEntry.BookingType, TFees.EntryFee, TFees.VehicleFee, TFees.GuideFee, TFees.CameraFee, TFees.TotalFee, TMainEntry.ServiceCode
    FROM TMainEntry, TVisitorComposition, TEntryDates, RoundsInfo, TFees
    WHERE TMainEntry.PermitNo=TVisitorComposition.PermitNo and TMainEntry.PermitNo=RoundsInfo.PermitNo and TMainEntry.PermitNo=TFees.PermitNo and TmainEntry.DateIssue between #01-sep-2006# and #30-nov-2006# and TMainEntry.PermitStatus<>'C'
    ORDER BY TMainEntry.DateIssue, TMainEntry.TimeIssue;
    Chances are that this is equivalent with (but much less performant than)
    Code:
    SELECT TmainEntry.DateIssue, TmainEntry.IndemNo, TmainEntry.PermitNo, TVisitorComposition.A12I, TVisitorComposition.C12I, TVisitorComposition.C5I, TVisitorComposition.A12F, TVisitorComposition.C12F, TVisitorComposition.C5F, TMainEntry.TotalVisitors, TMainEntry.NumVCam, TMainEntry.BookingType, TFees.EntryFee, TFees.VehicleFee, TFees.GuideFee, TFees.CameraFee, TFees.TotalFee, TMainEntry.ServiceCode
    FROM   TMainEntry, TVisitorComposition, TFees
    WHERE  TMainEntry.PermitNo=TVisitorComposition.PermitNo
      and  TMainEntry.PermitNo=TFees.PermitNo
      and  TmainEntry.DateIssue between #01-sep-2006# and #30-nov-2006#
      and  TMainEntry.PermitStatus<>'C'
    ORDER BY TMainEntry.DateIssue, TMainEntry.TimeIssue
    since I don't see any need to join with the tables RoundsInfo and TEntryDates, certainly not if you are going to remove duplicates (introduced by the presence of these tables) anyhow.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  8. #8
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by r937
    please be advised that the date column will also have a time component, but it will be set to midnight
    This is not the case for all relational database systems; so it might indeed be useful to use two columns, one for date and one for time, but only on systems where "date" does not store times and "time" does not store dates.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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