Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    Oct 2009
    Posts
    445
    Thanks
    7
    Thanked 3 Times in 3 Posts

    how to show 'all' duplicates in a single field

    I have got this far and would like to find out how I change the query so it shows all duplicates instead of just one of them.
    Code:
    SELECT count(*), `id`, `linklocation`, `link`
    		  FROM `links`
    		  WHERE `link` != ''
    		  GROUP BY `link` having count(*) > 1

  • #2
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,348
    Thanks
    23
    Thanked 618 Times in 617 Posts
    Needsome I am learning mysql also. Most of the time I ear mark a question and wait for Old Pendent to reply, but every once in a while I give it a shot.
    Try this and if it don't work we'll both wait for the master, OK?
    Code:
    SELECT *
    FROM links
    WHERE link IN (
    	SELECT link
    		FROM links
    		GROUP BY link
    		HAVING count(*) > 1
    )
    ORDER BY link

  • #3
    Regular Coder
    Join Date
    Oct 2009
    Posts
    445
    Thanks
    7
    Thanked 3 Times in 3 Posts
    Thank you sunfighter,

    I tried
    Code:
    SELECT *
    	FROM `links`
    	WHERE `link` IN (
    		SELECT `link`
    		FROM `links`
    		GROUP BY `link`
    		HAVING count(*) > 1
    	)
    	ORDER BY `id` DESC
    But it caused the server error 500, so I entered it in phpmyadmin and got no results.

  • #4
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,348
    Thanks
    23
    Thanked 618 Times in 617 Posts
    OK, We wait then.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,185
    Thanks
    80
    Thanked 4,453 Times in 4,418 Posts
    Although MySQL sloppily allows it, it is actually an error to use GROUP BY for other than *ALL* the non-aggregate fields in your SELECT.

    SO:
    Code:
    SELECT count(*), id, linklocation, link
    FROM links
    WHERE link != ''
    GROUP BY id, linklocation, link
    HAVING count(*) > 1
    Doing otherwise just cause MySQL to hide the truth from you.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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