CODEWITHSUNDEEP
sqlsql-server
Paralife
Paralife

Reputation: 6236

SQL Server comparison predicate on a composite primary key

In SQL Server, let's say I have a table Mytable with a composite primary key [ColA, ColB, ColC]. I would like to do the following pseudo-query: 

select * from Mytable where (ColA,ColB,ColC) > (SomeValA,SomeValB,SomeValC)

Is this possible somehow?

EDIT: Example:

create table Mytable (a integer, b integer, c integer);
insert into Mytable values (1,1,1),(1,1,2),(1,2,1),(2,1,1);

Doing select * from Mytable where (a,b,c) > (1,1,2) should return rows (1,2,1) and (2,1,1)

EDIT 2: Let me rephrase: If a total order function can be defined on an ordered subset of the columns of a table then is there a way I can query the table using this order function as a predicate?

Upvotes: 0

Views: 271

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

If you want to use indexes, then you might need:

select *
from Mytable 
where a > 1 
union all
select *
from Mytable 
where a = 1 and b > 1 
union all
select *
from Mytable 
where a = 1 and b = 1 and c > 2;

You could also add a persisted computed column and an index:

alter table MyTable add abc
    concat(format(a, '00000'), ':', format(b, '00000'), ':', format(c, '00000')) persisted;

create index idx_mytable_abc as mytable(abc);

Then the comparison would look like:

where abc > '00001:00001:00002'

and this can use the index.

Upvotes: 0

Thom A
Thom A

Reputation: 95554

I suspect this is what you are after:

--Sample Table
CREATE TABLE dbo.YourTable (ColA int,
                            ColB int,
                            ColC int);

--Sample Data    
WITH N AS
    (SELECT N
     FROM (VALUES (1),
                  (2),
                  (3),
                  (4),
                  (5),
                  (6),
                  (7),
                  (8),
                  (9),
                  (10)) N (N) )
INSERT INTO dbo.YourTable (ColA,
                           ColB,
                           ColC)
SELECT N1.N,
       N2.N,
       N3.N
FROM N N1,
     N N2,
     N N3;
GO

--Solution
DECLARE @I1 int,
        @I2 int,
        @I3 int;
SET @I1 = 5;
SET @I2 = 7;
SET @I3 = 2;


SELECT YT.ColA,
       YT.ColB,
       YT.ColC
FROM dbo.YourTable YT
WHERE YT.ColA > @I1
   OR (YT.ColA = @I1 AND YT.ColB > @I2)
   OR (YT.ColA = @I1 AND YT.ColB = @I2 AND YT.ColC > @I3)
ORDER BY YT.ColA,
         YT.ColB,
         YT.ColC;

GO

--Clean up
DROP TABLE dbo.YourTable;

Otherwise, to repoeat my comment: "In short: no. > requires scalar values on both sides." Syntax such as (ColA, ColB, ColC) > (@I1, @I2, @I3) is not value. SQL does not order in tuples; this includes in the ORDER BY. Syntax such as ORDER BY (ColA, ColB, ColC), OtherColumn would also error.

Upvotes: 1

Cetin Basoz
Cetin Basoz

Reputation: 23797

Probably easiest would be doing math. ie:

select * 
from myTable
where (a * 100) + (b * 10) + c > 112;

Upvotes: 0

Related Questions