Matrix query using INTERSECT

Question

I have data that represents real-world physical locations. Conceptually, one could look at it as:

  |--|---|---|---|---|---|
  |1A| 2A| 3A| 4A| 5A| 6A|
  |--|---|---|---|---|---|
  |1B| 2B| 3B| 4B| 5B| 6B|
  |--|---|---|---|---|---|
  |1C| 2C| 3C| 4C| 5C| 6C|
  |--|---|---|---|---|---|
  |1D| 2D| 3D| 4D| 5D| 6D|
  |--|---|---|---|---|---|
  |1E| 2E| 3E| 4E| 5E| 6E|
  |--|---|---|---|---|---|
  |1F| 2F| 3F| 4F| 5F| 6F|
  |--|---|---|---|---|---|

to play along, here is the DDL/DML:

CREATE TABLE [dbo].[test](
    [x] [int] NOT NULL,
    [y] [char](10) NOT NULL,
 CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED 
(
    [x] ASC,
    [y] ASC
)
) ON [PRIMARY]

GO

INSERT INTO Test(x,y) VALUES(1,'A');
INSERT INTO Test(x,y) VALUES(1,'B');
INSERT INTO Test(x,y) VALUES(1,'C');
INSERT INTO Test(x,y) VALUES(1,'D');
INSERT INTO Test(x,y) VALUES(1,'E');
INSERT INTO Test(x,y) VALUES(1,'F'); 

INSERT INTO Test(x,y) VALUES(2,'A');
INSERT INTO Test(x,y) VALUES(2,'B');
INSERT INTO Test(x,y) VALUES(2,'C');
INSERT INTO Test(x,y) VALUES(2,'D');
INSERT INTO Test(x,y) VALUES(2,'E');
INSERT INTO Test(x,y) VALUES(2,'F');

INSERT INTO Test(x,y) VALUES(3,'A');
INSERT INTO Test(x,y) VALUES(3,'B');
INSERT INTO Test(x,y) VALUES(3,'C');
INSERT INTO Test(x,y) VALUES(3,'D');
INSERT INTO Test(x,y) VALUES(3,'E');
INSERT INTO Test(x,y) VALUES(3,'F');  

INSERT INTO Test(x,y) VALUES(4,'A');
INSERT INTO Test(x,y) VALUES(4,'B');
INSERT INTO Test(x,y) VALUES(4,'C');
INSERT INTO Test(x,y) VALUES(4,'D');
INSERT INTO Test(x,y) VALUES(4,'E');
INSERT INTO Test(x,y) VALUES(4,'F');  

INSERT INTO Test(x,y) VALUES(5,'A');
INSERT INTO Test(x,y) VALUES(5,'B');
INSERT INTO Test(x,y) VALUES(5,'C');
INSERT INTO Test(x,y) VALUES(5,'D');
INSERT INTO Test(x,y) VALUES(5,'E');
INSERT INTO Test(x,y) VALUES(5,'F'); 

INSERT INTO Test(x,y) VALUES(6,'A');
INSERT INTO Test(x,y) VALUES(6,'B');
INSERT INTO Test(x,y) VALUES(6,'C');
INSERT INTO Test(x,y) VALUES(6,'D');
INSERT INTO Test(x,y) VALUES(6,'E');
INSERT INTO Test(x,y) VALUES(6,'F');

In truth, the matrix would be over a million X million but if I can solve this problem, I will be able to apply it to my application. The problem I have with my program is the amount of data to display for them to work with. Since, in this matrix, they are always interested in the periphery of the data grid, I am offering them the option to see a subset of the data on one of four sides of the grid. This is managed by a Top filter and the Order by clause.

I cannot use the data values of x or y. Only how they collate.

e.g.

  select top 2 *
  from test 
  order by y

returns the top two rows of the matrix drawn above. order by y desc returns the bottom two rows order by x asc returns the left two rows
order by x desc returns the right two rows

the above queries have made an unmanageable situation more manageable but now they want more.

I have been asked if there is any way to read in the corner of the matrix.

|1D| 2D| 3D|
|--|---|---|
|1E| 2E| 3E|
|--|---|---|
|1F| 2F| 3F|
|--|---|---|

It would seem that an intersect would do the trick but I am not finding success. I have tried an intersect with a recursive join and I am not having any luck.

It you seem that I could query the top 3 rows and the left 3 rows and then return only those that have the same value i.e. the intersection but I keep getting back full rows.

  select top 6 t1.x, t1.y
  from test t1
  inner join test t2 on (t1.x = t2.x) and (t1.y = t2.y)
  intersect
  select top 6 t2.x, t2.y
  from test t2
  inner join test t1 on (t1.x = t2.x) and (t1.y = t2.y)

It would seem that this is close, if I could get an Order by clause to work in both Selects.

Any help appreciated.

I understand what a corner means when you want exactly one cell. Why do you have `top 6`? — Gordon Linoff, Oct 17 '15 at 14:21

score 1 · Accepted Answer · answered Oct 17 '15 at 14:20

If you want the corners, why not just order by two columns? For the upper left corner:

select top 1 *
from test 
order by x, y;

Use asc and desc, as appropriate for the other corners. This can nicely take advantage of an index on x, y.

If you want cells from a square in a particular corner, then you can do:

select t.*
from test t
where t.x in (select top 3 t2.x from test t2 group by t2.x order by t2.x) and
      t.y in (select top 3 t2.y from test t2 group by t2.y order by t2.y);

This takes advantage of two indexes, test(x, y) and test(y, x).

Once again, use asc and desc in the subquery to define which corner.

FuzzyTree · Answer 2 · 2015-10-17T15:43:16.057

Here's another way using row_number() to give each cell an x,y coordinate, which allows you to select corners by specifying the coordinates in the where clause

select * from (
    select * , 
        row_number() over (partition by y order by x) xn,
        row_number() over (partition by x order by y) yn
    from test
) t where xn = 1 and yn = 4

Assuming 6 cells per row, the query above returns the top left corner of 1st 3x3 matrix in the 2nd row of matrices i.e. 1D. In general, to retrieve the top left corner of the nth matrix (size mxm) in the kth row of matrices you can use: where xn = ((n-1)*m)+1 and yn = ((k-1)*m)+1

To retrieve all top left corners of 3x3 matrices you can use the % operator where xn%3 = 1 and yn%3 = 1

I am not following you. Could you take it to the next logical step? — Meta Mussel, Oct 17 '15 at 15:24

Matrix query using INTERSECT

2 Answers2