A SQL Query to Get All the Records Where 5 Columns Are Same But Only One Column Is Different

Select rows with same id but different value in another column

This ought to do it:

SELECT *
FROM YourTable
WHERE ARIDNR IN (
    SELECT ARIDNR
    FROM YourTable
    GROUP BY ARIDNR
    HAVING COUNT(*) > 1
)

The idea is to use the inner query to identify the records which have a ARIDNR value that occurs 1+ times in the data, then get all columns from the same table based on that set of values.

How to select unique records from a 5 column table where the values can be the same but in different orders

Replace all =! with <:

Select t.col1, t1.col1, t2.col1, t3.col1, t4.col1
from table1 t 
CROSS JOIN table1 t1
CROSS JOIN table1 t2
CROSS JOIN table1 t3
CROSS JOIN table1 t4
WHERE
    t.col1 < t1.col1 and t.col1 < t2.col1 and t.col1 < t3.col1 and t.col1 < t4.col1 
    and t1.col1 < t2.col1 and t1.col1 < t3.col1 and t1.col1 < t4.col1 
    and t2.col1 < t3.col1 and t2.col1 < t4.col1
    and t3.col1 < t4.col1

The problem with =! is while you prevent numbers x and y from being the same, you get two joins; one for the case where x < y and one for the case where x > y, leading to unwanted duplicate joins.

Select rows with same value in one column but different value in another column

This solution uses a recursive CTE first to build the hierarchy and find the leading code for each row, even if a linked code points to a row which is pointing to an upper row itself.

The final query shows the count of different Salespersons:

DECLARE @tbl TABLE(CustCode INT,[Linked CustCode] INT,Salesperson VARCHAR(100));
INSERT INTO @tbl VALUES
 (100,100,'JASON')
,(200,100,'JASON')
,(300,300,'JIM')
,(400,300,'JIM')
,(500,100,'JASON')
,(600,300,'SUZY')
,(700,NULL,'JIM')
,(800,100,'JASON');

--The query

WITH CleanUp AS
(
    SELECT CustCode 
          ,CASE WHEN [Linked CustCode]=CustCode THEN NULL ELSE [Linked CustCode] END AS [Linked CustCode]
          ,Salesperson
    FROM @tbl
)
,recCTE AS
(
    SELECT CustCode AS LeadingCode,CustCode,[Linked CustCode],Salesperson
    FROM CleanUp
    WHERE [Linked CustCode] IS NULL

    UNION ALL

    SELECT recCTE.LeadingCode,t.CustCode,t.[Linked CustCode],t.Salesperson
    FROM recCTE
    INNER JOIN CleanUp AS t ON t.[Linked CustCode]=recCTE.CustCode
)
SELECT LeadingCode,COUNT(DISTINCT Salesperson) AS CountSalesperson
FROM recCTE
GROUP BY LeadingCode

The result

LeadingCode CountSalesperson
100         1
300         2
700         1

SQL/mysql - Select distinct/UNIQUE but return all columns?

You're looking for a group by:

select *
from table
group by field1

Which can occasionally be written with a distinct on statement:

select distinct on field1 *
from table

On most platforms, however, neither of the above will work because the behavior on the other columns is unspecified. (The first works in MySQL, if that's what you're using.)

You could fetch the distinct fields and stick to picking a single arbitrary row each time.

On some platforms (e.g. PostgreSQL, Oracle, T-SQL) this can be done directly using window functions:

select *
from (
   select *,
          row_number() over (partition by field1 order by field2) as row_number
   from table
   ) as rows
where row_number = 1

On others (MySQL, SQLite), you'll need to write subqueries that will make you join the entire table with itself (example), so not recommended.

SQL Query Multiple Columns Using Distinct on One Column Only

select * from tblFruit where
tblFruit_ID in (Select max(tblFruit_ID) FROM tblFruit group by tblFruit_FruitType)

Select multiple columns from a table, but group by one

I use this trick to group by one column when I have a multiple columns selection:

SELECT MAX(id) AS id,
    Nume,
    MAX(intrare) AS intrare,
    MAX(iesire) AS iesire,
    MAX(intrare-iesire) AS stoc,
    MAX(data) AS data
FROM Produse
GROUP BY Nume
ORDER BY Nume

This works.

How to select records without duplicate on just one field in SQL?

Try this:

SELECT MIN(id) AS id, title
FROM tbl_countries
GROUP BY title

---

Related Topics