For each name, find two smallest "ordering" values and subtract their "value" fields

Question

I'm sure there are better (more optimal) ways to solve this tricky SQL query practice task:

Take two lines with smallest "ordering"-values per every "name", sort them by "ordering"-fields, then subtract "value"-field of lines from each other.

I don't have slightest idea whether this can be done without three temporary tables, or even with just one or two complex queries... Please give some suggestions, or better implementations.

/* Create table to test the code below */

CREATE TABLE Testtable
(
tagID int primary key,
name varchar(32) not null,
value int not null,
ordering int not null
);

INSERT INTO Testtable (tagID, name,  value, ordering)
VALUES            (1,     'Artie', 5,   3);

INSERT INTO Testtable (tagID, name,  value, ordering)
VALUES            (2,     'Bertha', 3,   5);

INSERT INTO Testtable (tagID, name,  value, ordering)
VALUES            (3,     'Artie', 7,   2);

INSERT INTO Testtable (tagID, name,  value, ordering)
VALUES            (4,     'Denis', 9,   3);

INSERT INTO Testtable (tagID, name,  value, ordering)
VALUES            (5,     'Cecil', 2,   3);

INSERT INTO Testtable (tagID, name,  value, ordering)
VALUES            (6,     'Bertha ', 11,   7);

INSERT INTO Testtable (tagID, name,  value, ordering)
VALUES            (7,     'Artie', 12,   4);

INSERT INTO Testtable (tagID, name,  value, ordering)
VALUES            (8,     'Denis', 4,   5);

INSERT INTO Testtable (tagID, name,  value, ordering)
VALUES            (9,     'Denis', 1,   6);

From above table, I should get two smallest "ordering" per every "name", sorted:

3 Artie 7 2
1 Artie 5 3
2 Bertha 3 5
3 Bertha 11 7
4 Denis 9 3
8 Denis 4 5

And subtraction of rows should produce result: 2 -8 5 (because 7-5, 3-11 and 9-4)

My actual solution to the task:

/* Temporary table that is copy of Testtable except rows with only one instance of "name" are removed */

SELECT tagID, name, value, ordering
INTO #tempTable
FROM Testtable
/* Below does list of "names" that have 2+ rows in Testtable. Then WHERE-clause selects for final
#tempTable only rows where "name"-field matches to list of "names". */
WHERE name IN (SELECT name FROM Testtable GROUP BY name HAVING COUNT(name) >= 2);

/* Now I create two tables. First containing lowest values of "ordering", and second containing second
lowest values of "ordering". I couldn't figure out any other way to do it, except creating table with
lowest values and then reducing rows from that table from temporary table, and repeating fetch of lowest
values for remaining rows (which are now second lowest values). */

/* Fetch to lowest "ordering" to temporary table from each "name".
This collects all rows with "name" (because of GROUP BY), selects lowest "ordering" (because of MIN),
and creates to tempTableRowOne a row with "name" and lowest "ordering".
(Can't use more fields, this would result extra rows for each value in a field) */
SELECT name, value = 0, MIN(ordering) AS ordering
INTO #tempTableRowOne
FROM #tempTable
GROUP BY name;

/* Remove from tempTable rows that match with rows in tempTableRawOne. This leaves tempTable with
second (and above) lowest rows of "ordering" */
DELETE FROM #tempTable
WHERE EXISTS
(
SELECT * FROM #tempTableRowOne temp WHERE #tempTable.name = temp.name AND #tempTable.ordering = temp.ordering
);

/* Fetch to lowest (=second lowest) "ordering" to temporary table from each "name". */
SELECT name, value = 0, MIN(ordering) AS ordering
INTO #tempTableRowTwo
FROM #tempTable
GROUP BY name;

/* I would have rather written to tempTableRowOne's and tempTableRowTwo's "value"-field at previous step,
but I didn't figure how to do it. So now I have to fetch "value"-field afterwards when I know now the
"name" and "ordering"-fields for both lowest and second lowest "ordering" rows for each "name". */

UPDATE #tempTableRowOne
SET value = a.value
FROM Testtable a, #tempTableRowOne b
WHERE a.name = b.name AND a.ordering = b.ordering;

UPDATE #tempTableRowTwo
SET value = a.value
FROM Testtable a, #tempTableRowTwo b
WHERE a.name = b.name AND a.ordering = b.ordering;

/* Finally I can reduce rows from each other... */

SELECT a.value - b.value
FROM #tempTableRowOne a, #tempTableRowTwo b
WHERE a.name = b.name
ORDER BY a.name ASC;

DROP TABLE #tempTableRowOne
DROP TABLE #tempTableRowTwo
DROP TABLE #tempTable;
DROP TABLE Testtable;

Answer 1

This solution is based on Standard SQL & supported by most DBMSes (besides MySQL & Access):

WITH cte AS 
 (
   SELECT name, value, ordering,
      -- assign a sequence number to each row
      Row_Number() Over (PARTITION BY name ORDER BY ordering) AS rn,
      -- get the value from the next row 
      Lead(value) Over (PARTITION BY name ORDER BY ordering) AS nextval
   FROM testtable
 )
SELECT
   name, 
   value - nextval -- calculate the difference between current and next row
FROM cte
WHERE rn = 1              -- only return the row with the lowest ordering
  AND nextval IS NOT NULL -- when there are at least two rows

See fiddle at rextester

Answer 2

I believe that result is unpredictable: ordering in general case does not define the 2 specific rows; for that reason, value diff is quite a random.