Selecting Distinct Combinations

Selecting distinct combinations

Simply use the DISTINCT keyword:

SELECT DISTINCT Latitude, Longitude 
FROM Coordinates;

This will return values where the (Latitude, Longitude) combination is unique.

This example supposes that you do not need the other columns. If you do need them, i.e. the table has Latitude, Longitude, LocationName columns, you could either add LocationName to the distinct list, or use something along the lines of:

SELECT Latitude, Longitude, MIN(LocationName)
FROM Coordinates
GROUP BY Latitude, Longitude;

Selecting distinct 2 columns combination in mysql

Assuming that the first column is unique, you can do this:

SELECT id, col2, col3, col4
FROM yourtable
WHERE id IN
(
    SELECT MIN(id)
    FROM yourtable
    GROUP BY col2, col3
)

See it working online: sqlfiddle

All unique combinations of a column based on another one

There are duplicates in the table. So, the first step is to select distinct values. Well, and then just join the table to itself.

with pairs as (select distinct col1, col2 from mytable)
select a.col1, a.col2, b.col2
from pairs a
join pairs b on b.col1 = a.col1 and b.col2 > a.col2
order by a.col1, a.col2, b.col2;

How to pull distinct rows based on combination of few columns of a table

Creating some test data

DECLARE @Customer TABLE
(
    ACCT_NUM INT,
    LRN INT,
    name varchar(20),
    address varchar(100),
    state varchar(2),
    country varchar(100),
    city varchar(100)
)

INSERT INTO @Customer
VALUES
( 1, 1, 'Test1', 'Addr1', 'FL', 'USA', 'Tampa' ),
( 1, 1, 'Test1', 'Addr2', 'FL', 'USA', 'Tampa' ),
( 1, 1, 'Test1', 'Addr3', 'FL', 'USA', 'Tampa' ),
( 1, 1, 'Test1', 'Addr4', 'FL', 'USA', 'Tampa' ),
( 2, 1, 'Test2', 'Addr1', 'FL', 'USA', 'Tampa' ),
( 2, 1, 'Test2', 'Addr1', 'FL', 'USA', 'Tampa' ),
( 3, 1, 'Test3', 'Addr1', 'FL', 'USA', 'Tampa' )

I use rank to figure out all of the distinct combinations (if they are equal, rank would be equal as well)

 SELECT * FROM
 (
    SELECT *, 
        Rank() OVER (PARTITION BY c.ACCT_NUM, c.LRN ORDER BY c.Name, c.Address, c.State, c.Country, c.City) RK
    FROM @Customer c
) d
WHERE d.RK > 1

Output:

ACCT_NUM    LRN name    address state   country city    RK
1   1   Test1   Addr2   FL  USA Tampa   2
1   1   Test1   Addr3   FL  USA Tampa   3
1   1   Test1   Addr4   FL  USA Tampa   4

Select unique combinations of columns

You can use an EXISTS check to see if there is an entry in the table which has the same values but in a different order. We also add the condition that the F2 value is greater than the F1 value so that we don't exclude both (1,2) and (2,1), and we use DISTINCT so we don't get multiple entries where F1 = F2 (e.g. (2,2)).

SELECT DISTINCT F1, F2
FROM test t1
WHERE NOT EXISTS (SELECT F1, F2 
                  FROM test t2
                  WHERE t2.F1 = t1.F2 AND t2.F2 = t1.F1 AND t2.F2 > t2.F1)
ORDER BY F1, F2

Output:

F1  F2
1   2
1   3
4   1

Demo on SQLFIddle

Counting unique combinations of values across multiple columns regardless of order?

Assuming the character / doesn't show up in any of the offer names, you can do:

select count(distinct offer_combo) as distinct_offers
from (
  select listagg(offer, '/') within group (order by offer) as offer_combo
  from (
    select customer_id, offer_1 as offer from t
    union all select customer_id, offer_2 from t
    union all select customer_id, offer_3 from t
  ) x
  group by customer_id
) y

Result:

DISTINCT_OFFERS
---------------
2

See running example at db<>fiddle.

Select a unique combination of two columns

SELECT PRODUCTID,PACKINGID FROM DTEMP
GROUP BY PRODUCTID,PACKINGID
HAVING COUNT(PRODUCTID)=1 
ORDER BY 1;

You can try this one this is how i do in oracle ... to get the unique rows without using distinct.

---

Related Topics