Select All Rows With the Same Value in Column 1 But Different Values in Columns 2 and 3 Using SQL

Select all rows with the same value in column 1 but different values in columns 2 and 3 using SQL

Here is one approach:

WITH cte AS (
    SELECT [Order number]
    FROM mytable
    WHERE [SHIPPED/UNSHIPPED] IN ('SHIPPED', 'UNSHIPPED')
    GROUP BY [Order number]
    HAVING COUNT(DISTINCT [SHIPPED/UNSHIPPED]) = 2
)

SELECT *
FROM mytable
WHERE [Order number] IN (SELECT [Order number] FROM cte);

The CTE finds all order numbers which have both shipped and unshipped records. It works by first restricting a given order's records to only those having shipped/unshipped, then it asserts that the distinct count of that group is 2, implying both types of shipments are present.

Finding rows with same values in multiple columns

Try the following:

SELECT A.*
FROM YourTable A
INNER JOIN (SELECT Address, State
            FROM YourTable
            GROUP BY Address, State
            HAVING COUNT(*) > 1) B
ON A.Address = B.Address AND A.State = B.State

Select multiple rows with the same value(s)

You need to understand that when you include GROUP BY in your query you are telling SQL to combine rows. you will get one row per unique Locus value. The Having then filters those groups. Usually you specify an aggergate function in the select list like:

--show how many of each Locus there is
SELECT COUNT(*),Locus FROM Genes GROUP BY Locus

--only show the groups that have more than one row in them
SELECT COUNT(*),Locus FROM Genes GROUP BY Locus HAVING COUNT(*)>1

--to just display all the rows for your condition, don't use GROUP BY or HAVING
SELECT * FROM Genes WHERE Locus = '3' AND Chromosome = '10'

Find rows that have the same value on a column in MySQL

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
)

How to Select Every Row Where Column Value is NOT Distinct

This is significantly faster than the EXISTS way:

SELECT [EmailAddress], [CustomerName] FROM [Customers] WHERE [EmailAddress] IN
  (SELECT [EmailAddress] FROM [Customers] GROUP BY [EmailAddress] HAVING COUNT(*) > 1)

Select all rows containing duplicate values in one of two columns from within distinct groups of related records

This type of query can be implemented as a semi join.

Semijoins are used to select rows from one of the tables in the join.

For example:

select distinct l.*
from payment l
inner join payment r
on 
  l.id != r.id and l.account = r.account and
  (l.date = r.date or l.amount = r.amount)
where l.status != 'X' and r.status != 'X'
order by l.id asc;

Note the use of distinct, and that I'm only selecting columns from the left table. This ensures that there are no duplicates.

The join condition checks that:

• it's not joining a row to itself (l.id != r.id)
• rows are in the same account (l.account = r.account)
• and either the date or the amount is the same (l.date = r.date or l.amount = r.amount)

---

For the second part of your question, you would need to update the on clause in the query.

---

Related Topics