CODEWITHSUNDEEP
sqldatabase
DucCuong
DucCuong

Reputation: 658

SQL - How to list all tuples in a relation such that tuple 1 is greater than tuple 2

Suppose that I have a relation with only 1 column "Value (INT)" and its values are in descending order.

+----------+
+  VALUE   +
+----------+
+    10    +
+    9     +
+    8     +
+    7     +
....

How can list all the combinations which contains two tuples such that the first tuple is greater than the second tuple

Note: It may exist two tuples with same value

The desired outputs should be like: (10,9) (10, 8) (9,8), (9,7) (8,7)

Upvotes: 0

Views: 1467

Answers (2)

maciek
maciek

Reputation: 541

I understand that a single tuple may apear only twice on the left side of the resultset, am I right? That's why there is no (10,7)? Then you need to compute row number.

select t1.value, t2.value
from
(
    select t.value, row_number(order by t.value) as rnum
    from table t
) t1 inner join
(
    select t.value, row_number(order by t.value) as rnum
    from table t
) t2 on t1.value > t2.value and t1.rnum < t2.rnum + 2

Performance of this query will be pretty bad, but I don't know what database are you using - I've used MS SQL row_number function.

Another idea: If you are using SQL Server 2012+ and your answer to the question posed at the begining of this post is positive, you can use:

select t.value, lead(t.value,1,0) over(order by t.value desc) as lead1
    lead(t.value,2,0) over(order by t.value desc) as lead2
from table t

You may need to handle 0 (defulat value if there is no "lead" tuple). I'm not sure if output in this form is acceptable.

And here you go with cursor solution:

DECLARE @result TABLE
(
    value1 int,
    value2 int
);
DECLARE
    @value int,
    @lag1 int,
    @lag2 int

DECLARE c CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR
SELECT value
FROM table
ORDER BY value desc
OPEN c;

FETCH NEXT FROM c INTO @lag2;
FETCH NEXT FROM c INTO @lag1;

FETCH NEXT FROM c INTO @value;

WHILE @@FETCH_STATUS = 0
BEGIN

    INSERT @result(value1, value2) SELECT @lag2, @lag1
    INSERT @result(value1, value2) SELECT @lag2, @value

    SET @lag2 = @lag1
    SET @lag1 = @value

    FETCH NEXT FROM c INTO @value

END
CLOSE c;

Again, I used MS SQL syntax. If you write how you want duplicates handled, I can update the solution.

Upvotes: 0

Explosion Pills
Explosion Pills

Reputation: 191789

You can do a cross join on the same table.

SELECT t1.VALUE AS VALUE1, t2.VALUE AS VALUE2
FROM thing t1 JOIN thing t2 ON (t1.VALUE != t2.VALUE AND t1.VALUE > t2.VALUE)

Upvotes: 3

Related Questions