SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    865
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Question about using a datetime column with an unique index

    I was looking for duplicates in my table and grouped the rows by my datetime column. I thought this would only find rows that had the exact same date and time, down to the second. However, I learned this was not the case. It found duplicates for just the date. Here is what I used:

    Code:
    select dateAdded, uID, type, subject, message, count(*) as total from notifications group by uID, type, subject, message having total > 1 order by type;
    How come this works fine and yet an unique index on the dateAdded column will only prevent duplicate inserts if the date and time are exact, down to the second?

    Thanks!
    Convert your dollars into silver coins. www.convert2silver.com

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by busboy View Post
    It found duplicates for just the date.
    nope

    it found duplicates for the uID, type, subject, message column values

    the dateAdded value it displayed was a random one from all the rows that had the same uID, type, subject, message
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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