WEB SQL question - Distinct.

Discussion in 'OT Technology' started by brds, Apr 8, 2009.

  1. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,652
    Likes Received:
    15
    Location:
    Atlanta
    I'm creating a 404 application that will monitor and record any 404 that is thrown to the user. I have a DB with 5 columns in it (ID, DATE, IP, REFERER, and ATTEMPTEDPAGE).

    In the admin toolkit (where the admin will go to view the 404's in order to fix them), I'd like to display a table of only unique errors. For example... if the same referring page (call it page A) causes a page (say, page B) to 404 a thousand times, I'd only want it to show up once. However.... if there are 10 pages that link to page b, i'd like to show all of them.

    I tried playing around with distinct, but couldn't get it just right. Anybody able to help me out here?


    Example of Database
    Code:
    ID          DATE          IP          REFERER          ATTEMPTEDPAGE
    1          1/1/2001   1.5.3.4      Page1.cfm        Page2.cfm
    2          1/1/2001   1.2.3.4      Page1.cfm        Page2.cfm
    3          1/1/2001   1.8.3.4      Page1.cfm        Page2.cfm
    4          1/1/2001   1.2.3.4      Page1.cfm        Page2.cfm
    5          1/1/2001   1.2.5.4      Page3.cfm        Page2.cfm
    6          1/1/2001   1.2.3.4      Page4.cfm        Page2.cfm
    7          1/1/2001   1.2.0.4      Page3.cfm        Page2.cfm
    
    So in the example above, all i would want on the query return would be ID#'s 1, 5, and 6 since everything else is just a duplicate of one of those 3.
     
  2. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    GROUP BY Referer
     
  3. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    and WTF cold fusion
     
  4. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    what kind of db btw? assuming mysql?
     
  5. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,652
    Likes Received:
    15
    Location:
    Atlanta
    :wtc:

    Dunno why it's saying that. I didn't even think of doing it the way you did, but it seems like it should work.
     
  6. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,652
    Likes Received:
    15
    Location:
    Atlanta
    Microsoft SQL
     
  7. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,652
    Likes Received:
    15
    Location:
    Atlanta
    Government job... work for FSU
     
  8. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,652
    Likes Received:
    15
    Location:
    Atlanta
    this works, but i doesn't return any of the other columns and if i put them in there like this:

    The query no longer works
     
  9. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    what data do you really need returned? pretty easy with a group by unless you need all the id's and stuff. i.e. do you want to see HOW MANY unique 404's were thrown per referer?
     
  10. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,652
    Likes Received:
    15
    Location:
    Atlanta
    Fuckin' tell me about it
     
  11. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    retuning the id's wouldn't make much sense if you're only showing the distinct instances of referrer
     
  12. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,652
    Likes Received:
    15
    Location:
    Atlanta
    My boss wants to know the stats... I want to know the referer/attempted page... so yeah, i need it all
     
  13. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,652
    Likes Received:
    15
    Location:
    Atlanta
    ID's aren't THAT important... i just use them sometimes to help with viewing an individual item... IE a more info link
     
  14. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    to me, this would be the more useful to see:

    SELECT referrer, count(*) as [TimesOccurred]
    FROM mytable
    GROUP BY referrer
     
  15. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,652
    Likes Received:
    15
    Location:
    Atlanta
    Nope... i tried that before (mentioned above) and it returns everything b/c the ID's are different... for some reason it's like the distinct is applying itself to everything... not just the referer + attemptedpage
     
  16. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,652
    Likes Received:
    15
    Location:
    Atlanta
    I want to return every distinct pair of attemptedpage and referer combinations despite what the ID number is.
     
  17. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,652
    Likes Received:
    15
    Location:
    Atlanta
    yep
     
  18. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,652
    Likes Received:
    15
    Location:
    Atlanta
    That just returns a count of how many times "Referer" shows up... which is the number of entries in the table.

    I'm think I could just concat it all and then just use some CFM array functions to delete the duplicates. It's not the best way to do it, but it'll work.
     
  19. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,652
    Likes Received:
    15
    Location:
    Atlanta
    Time to go home. I'll check back on the thread tomorrow... thanks for the help guys!
     
  20. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,652
    Likes Received:
    15
    Location:
    Atlanta
    No because it doesn't return the ID, IP, or Date.
     
  21. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,652
    Likes Received:
    15
    Location:
    Atlanta
    Still showed all results (i'm guessing b/c the ID's are different).... thanks for the help anyways. I'll just use the Coldfusion function that eliminatees duplicates in an array.
     

Share This Page