CODEWITHSUNDEEP
sql
Bhav
Bhav

Reputation: 2207

SELECT only partially distinct data

How can you do a SELECT statement with a partial distinct?

Col1    Col2    Col3    Col4
data1   data2   data3   abc
data2   data3   data2   abcde
data1   data2   data3   abcfef3f

For the dataset above, only return those with a distinct (Col1, Col2, Col3):

Col1    Col2    Col3    Col4
data1   data2   data3   abc
data2   data3   data2   abcde

Ultimately I want to insert this into a temp table like below.

INSERT INTO #tmp
(
    [Col1],
    [Col2],
    [Col3],
    [Col4]
)
SELECT DISTINCT Col1, Col2, Col3, Col4
FROM        store s
INNER JOIN  address a ON s.addressid = a.id

Upvotes: 1

Views: 236

Answers (2)

DanB
DanB

Reputation: 2124

You also can use CTE with Ranking function to do that.

; with subQry as (
    select col1, col2, col3, col4, rownumber() over(partition by col1, col2, col3 order by col1, col2, col3) rnk
    FROM        store s
    INNER JOIN  address a ON s.addressid = a.id
)
select * from subQry where rnk = 1;

Upvotes: 1

nos
nos

Reputation: 229234

since you don't care which of col4 values you want, you could get the smallest one, and do

SELECT Col1, Col2, Col3, min(Col4)
FROM        store s
INNER JOIN  address a ON s.addressid = a.id
GROUP BY Col1, Col2, Col3

Upvotes: 3

Related Questions