SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict
    Join Date
    Jan 2012
    Posts
    265
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How 2 find duplicate rows (PHP/MySQL)

    I have a spreadsheet with a lot of data that I save as a CSV file and import into a MySQL database table. One column contains dynamic URL's for my website, like this...

    Arizona
    Arizona_Flower
    Arizona_Flower
    Arizona_Bird

    I want to retain the duplicate rows (e.g. Arizona_Flower) in this particular table. However, I need to create an additional table that features this column only, and without duplicates. In fact, this will be the table that creates the pages.

    So I'd like to know if there's a quick, easy way of locating duplicate rows. I don't want to delete them (yet), as I need to find the same rows in my Excel spreadsheet and mark them accordingly. In other words, I'm going to use the same spreadsheet to create both tables. So I simply want to mark the duplicate rows as duplicates and sort them to the bottom of the file when I'm creating the table without duplicate rows.

    So imagine if my spreadsheet, and the database table I create with it, have the following duplicate rows:

    Arizona_Flower
    Arizona_Flower
    California_Introduction
    California_Introduction
    Japan
    Japan

    Is there some sort of query I can write that will display those rows and those rows only?

    (I'm aware of the GROUP BY function, which I can use as a last resort. I just thought it might be a little time consuming. I'd have to display a list of rows via GROUP BY, then paste it into my spreadsheet and compare it to the column with the duplicates. It would be faster if I can simply get a list of duplicate rows.)

    Thanks!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    a list of duplicate rows when there's only one column in the row is trivial...
    Code:
    SELECT dynamic_url
      FROM daTable
    GROUP
        BY dynamic_url
    HAVING COUNT(*) > 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Jan 2012
    Posts
    265
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow, thanks for the tip! That's going to help me clean up all my database tables.


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
  •