Sql-server – How could you implement a custom sort that groups matching records from other source

order-bysql serversql-server-2012

This is obviously simplified. I have 2 applications: Driver and Match. I'm presenting data to users in this order and asking them to select which of the people in the Match application is truly a match for the person in the Driver application.
Data needs to be ordered as you see below: Driver rows need to be in alpha order by PersonName. Below each Driver record, list the Match records that have the same PersonMatchID, in PersonName alpha order. Some Driver records will have no corresponding Match records.

If you started with the rows below in random order, how would you write a SQL query to return them in this order? I am dealing with about 3000 rows at most, so I don't have to worry overmuch about optimization.

Source-system    PersonName    PersonPossibleMatchID
-------------    ----------    ---------------------
Driver           AA            77777
  Match          AA            77777
Driver           AB            11111
  Match          AA            11111
Driver           BB            33333
  Match          BA            33333
  Match          BB            33333
Driver           CC            99999
  Match          CB            99999
  Match          CC            99999
  Match          CD            99999
Driver           DD            44444
Driver           EE            22222

I've tried

SELECT * ORDER BY LEFT(PersonName,1), PersonPossibleMatchId,
Source-system

but this doesn't return Driver records in strictly alpha order – you get all the A's first, but within that the order on second letter is randomized. For example, in the above list, I might have Driver AB listed prior to Driver AA.

Added: I'm using MS SQL (2012)

Best Answer

Here's another couple of possibilities

SELECT [Source-System],
       PersonName,
       PersonPossibleMatchID
FROM   #src
ORDER  BY MAX(CASE
                WHEN [Source-system] = 'Driver'
                  THEN PersonName
              END) OVER (PARTITION BY PersonPossibleMatchId) ASC,
          [Source-System] DESC,
          PersonName ASC

(online demo)

- Or -

WITH DriverPersonNames
     AS (SELECT PersonPossibleMatchID,
                PersonName
         FROM   #src
         WHERE  [Source-System] = 'Driver')
SELECT s.*
FROM   #src s
       JOIN DriverPersonNames d
         ON d.PersonPossibleMatchID = s.PersonPossibleMatchID
ORDER  BY d.PersonName ASC,
          [Source-System] DESC,
          s.PersonName ASC

(online demo)

Both will return the same results assuming that there is exactly one Driver record per PersonPossibleMatchID and PersonPossibleMatchID is not nullable.

(DDL for online demos borrowed from jyao's answer)

Related Solutions

Sql-server – Query to return fields of distinct values per key

Without knowing anything about the source data, perhaps this would do what you want?

USE Test;
GO
CREATE TABLE GENDER
(
    ORG INT NOT NULL
    , GENDER VARCHAR(1) NOT NULL
);

CREATE TABLE AGE
(
    ORG INT NOT NULL
    , AGE TINYINT
);

CREATE TABLE STATES
(
    ORG INT NOT NULL
    , STATENAME VARCHAR(255)
);

INSERT INTO Gender (ORG, GENDER) VALUES (1, 'M');
INSERT INTO Gender (ORG, GENDER) VALUES (1, 'F');
INSERT INTO Gender (ORG, GENDER) VALUES (2, 'M');
INSERT INTO Gender (ORG, GENDER) VALUES (2, 'F');
INSERT INTO Gender (ORG, GENDER) VALUES (3, 'M');
INSERT INTO Gender (ORG, GENDER) VALUES (3, 'F');

INSERT INTO AGE (ORG, AGE) VALUES (1,27);
INSERT INTO AGE (ORG, AGE) VALUES (1,28);
INSERT INTO AGE (ORG, AGE) VALUES (1,29);
INSERT INTO AGE (ORG, AGE) VALUES (1,30);
INSERT INTO AGE (ORG, AGE) VALUES (2,37);
INSERT INTO AGE (ORG, AGE) VALUES (2,38);
INSERT INTO AGE (ORG, AGE) VALUES (2,39);
INSERT INTO AGE (ORG, AGE) VALUES (2,40);
INSERT INTO AGE (ORG, AGE) VALUES (3, 2);

INSERT INTO STATES (ORG, STATENAME) VALUES (1,'FL');
INSERT INTO STATES (ORG, STATENAME) VALUES (1,'GA');
INSERT INTO STATES (ORG, STATENAME) VALUES (1,'MN');
INSERT INTO STATES (ORG, STATENAME) VALUES (1,'NM');
INSERT INTO STATES (ORG, STATENAME) VALUES (2,'FL');
INSERT INTO STATES (ORG, STATENAME) VALUES (2,'MN');
INSERT INTO STATES (ORG, STATENAME) VALUES (2,'NM');
INSERT INTO STATES (ORG, STATENAME) VALUES (3,'FL');
INSERT INTO STATES (ORG, STATENAME) VALUES (3,'GA');
INSERT INTO STATES (ORG, STATENAME) VALUES (3,'NM');

CREATE TABLE FACTS
(
    ORG INT NOT NULL
    , GENDER VARCHAR(1) NULL
    , AGE INT NULL
    , STATENAME VARCHAR(255) NULL
);

INSERT INTO FACTS (ORG, GENDER, AGE, STATENAME)
SELECT ORG, GENDER, NULL, NULL
FROM GENDER
GROUP BY ORG, GENDER
UNION ALL
SELECT ORG, NULL, AGE, NULL
FROM AGE
GROUP BY ORG, AGE
UNION ALL
SELECT ORG, NULL, NULL, STATENAME
FROM STATES;

SELECT *
FROM FACTS
ORDER BY ORG;

The results:

enter image description here

This will create a FACTS table that has all the data from several source tables. As @ypercube and @jon-seigel said, this really doesn't make much sense; perhaps we are missing something compelling about your setup.

If this is not what you were expecting, please provide the source tables, and any other pertinent details.

SQL Server – How to Query Transfers for Single Source to Single Destination

I don't know all of your source data (or why there isn't any type of unique constraint that would prevent full-on duplicates or a source with multiple destinations), but given only the sample data supplied:

;WITH s AS 
(
  -- first let's eliminate duplicates
  SELECT DISTINCT Source, Destination 
    FROM dbo.MyTable
)
SELECT Source, Destination
FROM s
WHERE NOT EXISTS
(
  SELECT 1 FROM s AS d WHERE 

  -- eliminate chains in either direction:
    d.Destination = s.Source OR d.Source = s.Destination

  -- eliminate any source with multiple destinations:
    OR (d.Source = s.Source AND d.Destination <> s.Destination)

  -- eliminate any destination with more than one source
    OR (d.Destination = s.Destination AND d.Source <> s.Source)
);

SQL fiddle demo

Best Answer

Related Solutions

Sql-server – Query to return fields of distinct values per key

SQL Server – How to Query Transfers for Single Source to Single Destination

Related Question