CODEWITHSUNDEEP
sqlsql-servert-sql
SashaSQL
SashaSQL

Reputation: 33

Sorting by Duplicates in Two Columns

I have a list of names and phone numbers. There are duplicates in both columns.

With the following code, I get the table sorted by phone, but if there are any name duplicates, those are clustered together:

SELECT 
    a.[name], 
    a.phone
FROM 
    Tests.dbo.sort_test6 AS a
JOIN 
(
    SELECT 
        [name], 
        MIN(phone) AS min_phone_by_name
    FROM 
        Tests.dbo.sort_test6 
    GROUP BY 
        [name]
) AS dt
ON a.[name] = dt.[name]
ORDER BY min_phone_by_name, [name], phone

The result is very good; it's all in order by phone, but the names are clustered if there are any duplicates:

name phone
Eve 098
Eve 365
Eve 789
Nack 098
Adam 123
Adam 345
Adam 346
Oiu 145
Tom 145
Nic 146
James 156
Sam 177
Sam 443
Sam 777
Tim 256
Will 256
Sara 333
Dav 345

Phone 345 that belongs to Adam and Dav is my issue.

What I want is for that 345 to be brought up to the 345 in the Adam cluster, but only at the end of the Adam cluster. So exactly like this:

name phone
Eve 098
Eve 365
Eve 789
Nack 098
Adam 123
Adam 345
Adam 346
Dav 345
Oiu 145
Tom 145
Nic 146
James 156
Sam 177
Sam 443
Sam 777
Tim 256
Will 256
Sara 333

EDIT: Here is the table I'm working with if this will help:

CREATE TABLE Tests.dbo.sort_test6
(
    name VARCHAR(10),
    phone CHAR(3)
)

INSERT INTO Tests.dbo.sort_test6
VALUES
('Adam', '345'),
('Adam', '345'),
('Adam', '123'),
('Dav', '345'),
('Dav', '999'),
('Dav', '555'),
('Dav', '699'),
('Dav', '112'),
('Dav', '112'),
('Dav', '112'),
('Dav', '112'),
('Rob', '456'),
('Rick', '645'),
('Rick', '876'),
('Eve', '365'),
('Eve', '098'),
('Eve', '789'),
('Si', '876'),
('Will', '256'),
('Chad', '876'),
('James', '156'),
('Dirk', '844'),
('Chaz', '513'),
('Dob', '513'),
('Ron', '513'),
('Gil', '513'),
('Jack', '725'),
('Nack', '098'),
('Oiu', '145'),
('Uva', '765'),
('Tom', '145'),
('Chaz', '736'),
('Chaz', '875'),
('Chaz', '875'),
('Chaz', '875'),
('Chaz', '875'),
('Chaz', '875'),
('Chaz', '875'),
('Chaz', '731'),
('Laz', '763'),
('Nic', '146'),
('Sam', '177'),
('Sam', '777'),
('Sam', '443'),
('Chim', '777'),
('Sara', '333'),
('Tim', '256'),
('Ted', '731'),
('zed', '731'),
('Tam', '146'),
('Zam', '146'),
('Lam', '146'),
('Tom', '215'),
('Sam', '146')

Upvotes: 1

Views: 91

Answers (1)

Sean Lange
Sean Lange

Reputation: 33581

There are several ways to accomplish this. Here is my take on this problem. Also, you have "Dav" at the wrong place in your desired output according to your explanation.

with SortedResults as
(
    select *
        , RowNum = ROW_NUMBER() over (partition by name order by phone)
    from SortDemo
)
select sd.name
    , sd.phone
from SortedResults sr
join SortDemo sd on sd.name = sr.name
where sr.RowNum = 1
order by sr.phone
    , sd.name

Upvotes: 1

Related Questions