SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    Michigan
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Wanting distinct rows (by only one column), not sure how to do it

    I have a table `logins` with id, name, ip, mac, date

    ip and mac are both tracking information for name and I'm trying to write a query that, when given a name, selects all names that person has logged in with by cross-checking their ip and mac with every record we have for that name

    I have this query so far but it gives duplicates in the name field. What I'm trying to do is get it to give me each name only once and the most recent date associated with that name.

    Thanks guys!

    Code:
    SELECT *
    FROM `login_history`
    WHERE `ip`
    IN (
    
    SELECT `ip`
    FROM `login_history`
    WHERE `name` = 'Name'
    )
    OR `mac`
    IN (
    
    SELECT `mac`
    FROM `login_history`
    WHERE `name` = 'Name'
    )

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select one.*
      from login_history as one
    inner
      join login_history as two
        on two.ip = one.ip
        or two.mac = one.mac 
     where two.name = 'Name'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    Michigan
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the help but a slight problem!

    I have a total of 27 rows in my table, with only 6 with name = 'Name', and 11 more rows where the name is not = 'Name' but where the ip or mac matches one of the 6's ip/mac.

    The query returned 102 results.

  4. #4
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Rather than 'select one.*', try 'select distinct one.name' in that query?

  5. #5
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    Michigan
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Still getting 17 results with many repeats.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    perhaps you could give us a few sample rows of data, and show what the results should be, so that we can see exactly what the query needs to do
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    Michigan
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ill work on it. I meant to do so however been busy with the holidays.

    Thanks for the help so far guys.

  8. #8
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    Michigan
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Lets assume we are looking for N1. My date column is just a timestamp, so lets also assume bigger date = newer. Rows I want returned are marked with a * and I left a few comments explaining why.
    Code:
       |  id |  name  |  ip   |  mac   |  date
    ---|-----|--------|-------|--------|-------
       |  1  |  N1    |  IP1  | MAC1   |  1        // This matches name. NOT selected because row #3 has same name, but newer date
       |  2  |  N2    |  IP2  | MAC2   |  2
    *  |  3  |  N1    |  IP3  | MAC3   |  3        // This matches name
    *  |  4  |  N3    |  IP3  | MAC4   |  4        // This IP matches row #3 IP
       |  5  |  N4    |  IP1  | MAC5   |  5        // This IP matches row #1 IP. NOT selected, row #7 has newer date (it would be if row #7 didn't exist)
       |  6  |  N5    |  IP4  | MAC6   |  6
    *  |  7  |  N4    |  IP5  | MAC1   |  7        // This matches row #1 MAC
       |  8  |  N6    |  IP6  | MAC7   |  8
    If I were to do this in code with multiple selects it would be like so, in somewhat psuedocode:

    select all IPs and all MACs where name matches N1
    select all rows where the IP is in the first select
    select all rows where the MAC is in the first select
    combine the results of the second and third and remove duplicate names, keeping the name with the biggest date


    Hope this makes sense, if not I'll try explaining again.

    Thanks and happy holidays.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Pests View Post
    ...keeping the name with the biggest date
    this is new information, so of course it means a change in the query
    Code:
    select one.*
      from login_history as one
    inner
      join login_history as two
        on two.ip = one.ip
        or two.mac = one.mac 
     where two.name = 'Name'
       and one.date =
           ( select max(date)
               from login_history
              where name = one.name ) 
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Enthusiast
    Join Date
    Apr 2004
    Location
    Michigan
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I did mention it before (in the original post), perhaps I worded it wrong or something:

    I have this query so far but it gives duplicates in the name field. What I'm trying to do is get it to give me each name only once and the most recent date associated with that name.
    That seems to be working much better but I'm thinking if you missed the date portion above you might have also missed the "each name only once".

    I added distinct to the first select clause and now it seems to be working as expected, although I'm not entirely sure since I'm not too knowledgeable about distinct.

    Thanks for all your help and sorry it took so long.

    Edit:

    Quick question: Would it be possible to dynamically a column signaling whether the row was matched by either ip or mac? Or something similar to this?

    Thanks.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    you are right, you did mention it, i'm sorry

    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
  •