Indexes are used to find rows with specific column values quickly. Simply put, an index is a pointer to data in a table.
The order that rows are returned in is guaranteed ONLY by ORDER BY clause (or in MySQL, an ORDER BY implicitly specified in the GROUP BY clause.) Apart from that, there is NO GUARANTEE of the order rows will be returned in. Apart from that, MySQL is free to return the rows in any sequence.
To select duplicate values, you need to create groups of rows with the same values and then select the groups with counts greater than one. You can achieve that by using GROUP BY and a HAVING clause.
This query will give you a list of email addresses and how many times they're used, with the most used addresses first.
SELECT email,
count(*) AS c
FROM TABLE
GROUP BY email
HAVING c > 1
ORDER BY c DESC
If you want the full rows:
select * from table where email in (
select email from table
group by email having count(*) > 1
)
select email from mytable group by email having count(*) >1
Here is query to find email
's which are used for more then one login_id
:
SELECT email
FROM table
GROUP BY email
HAVING count(*) > 1
You'll need second (of nested) query to get list of login_id
by email
.
First part of accepted answer does not work for MSSQL.
This worked for me:
select email, COUNT(*) as C from table
group by email having COUNT(*) >1 order by C desc
use this if your email column contains empty values
select * from table where email in (
select email from table group by email having count(*) > 1 and email != ''
)
Thanks guys :-) I used the below because I only cared about those two columns and not so much about the rest. Worked great
select email, login_id from table
group by email, login_id
having COUNT(email) > 1
I know this is a very old question but this is more for someone else who might have the same problem and I think this is more accurate to what was wanted.
SELECT * FROM member WHERE email = (Select email From member Where login_id = [email protected])
This will return all records that have [email protected] as a login_id value.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With