CODEWITHSUNDEEP
sqlsql-server
Ironsun
Ironsun

Reputation: 881

SQL get the closest two rows within duplicate rows

I have following table

ID   Name      Stage
1    A           1      
1    B           2                 
1    C           3  
1    A           4
1    N           5
1    B           6
1    J           7 
1    C           8
1    D           9
1    E           10

I need output as below with parameters A and N need to select closest rows where difference between stage is smallest

   ID   Name      Stage
    1    A           4      
    1    N           5

I need to select rows where difference between stage is smallest

Upvotes: 1

Views: 87

Answers (2)

Sal-laS
Sal-laS

Reputation: 11649

This solution works, if u know the minimum difference is always 1

    SELECT * 
    FROM myTable as a
    CROSS JOIN myTable as b
    where a.stage-b.stage=1;


     a.ID   a.Name      a.Stage  b.ID   b.Name      b.Stage
        1    A           4        1       N             5

Or simpler if u don't know the minimum

SELECT *
FROM myTable as a
    CROSS JOIN myTable as b
where a.stage-b.stage in (SELECT min (a.stage-b.stage) 
    FROM myTable as a
    CROSS JOIN myTable as b)

Upvotes: 1

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657962

This query can make use of an index on (name, stage) efficiently:

WITH cte AS (
   SELECT TOP 1
          a.id AS a_id, a.name AS a_name, a.stage AS a_stage
        , n.id AS n_id, n.name AS n_name, n.stage AS n_stage
   FROM   tbl a
   CROSS  APPLY  (
      SELECT TOP 1 *, stage - a.stage AS diff
      FROM   tbl  
      WHERE  name = 'N'
      AND    stage >= a.stage
      ORDER  BY stage

      UNION ALL
      SELECT TOP 1 *, a.stage - stage AS diff
      FROM   tbl  
      WHERE  name = 'N'
      AND    stage < a.stage
      ORDER  BY stage DESC
      ) n
   WHERE  a.name = 'A'
   ORDER  BY diff
   )
SELECT a_id AS id, a_name AS name, a_stage AS stage FROM cte
UNION ALL
SELECT n_id, n_name, n_stage FROM cte;

SQL Server uses CROSS APPLY in place of standard-SQL LATERAL.

In case of ties (equal difference) the winner is arbitrary, unless you add more ORDER BY expressions as tiebreaker.

dbfiddle here

Upvotes: 1

Related Questions