Filter a table to get unique rows in a certain logic

• moadh.bs

  SSC Veteran

  Points: 299

  More actions

  January 19, 2011 at 3:42 am

  #233065

  Hi,

  I'm trying to select specific rows from this table:

  CREATE TABLE MVTS (idMvt1 int,

  idMvt2 int,

  idMvt3 int);

  INSERT INTO MVTS

  SELECT 271, 204, 136

  UNION ALL

  SELECT 271, 202, 136

  UNION ALL

  SELECT 268, 253, 204

  UNION ALL

  SELECT 268, 253, 202

  UNION ALL

  SELECT 268, 256, 204

  UNION ALL

  SELECT 268, 256, 202

  the output expected is the first row and the fourth row

  to explain the logic let's imagine we have a second table A to put in the selected id's

  next w'll iterate throw MVTS :

  - first row 271, 204, 136 ; none of this ids exists in A

  So w'll select this row and put the 3 ids in A

  - second row 271 exist already in A so w'll ignore this row

  - third row 204 exists

  - fourth row 268, 253, 202 none of this ids exists in A

  So w'll select this row and put the 3 ids in A

  in the end we should have only the first and the fourth row

  I'm looking for a performant T-sql solution without Cursor and iteration because the average amount of row is by millions

  thx

• Jeff Moden

  SSC Guru

  Points: 1004339

  More actions

  January 19, 2011 at 4:44 am

  #1275201

  moadh.bs (1/19/2011)

  ---

  Hi,

  I'm trying to select specific rows from this table:

  CREATE TABLE MVTS (idMvt1 int,

  idMvt2 int,

  idMvt3 int);

  INSERT INTO MVTS

  SELECT 271, 204, 136

  UNION ALL

  SELECT 271, 202, 136

  UNION ALL

  SELECT 268, 253, 204

  UNION ALL

  SELECT 268, 253, 202

  UNION ALL

  SELECT 268, 256, 204

  UNION ALL

  SELECT 268, 256, 202

  the output expected is the first row and the fourth row

  to explain the logic let's imagine we have a second table A to put in the selected id's

  next w'll iterate throw MVTS :

  - first row 271, 204, 136 ; none of this ids exists in A

  So w'll select this row and put the 3 ids in A

  - second row 271 exist already in A so w'll ignore this row

  - third row 204 exists

  - fourth row 268, 253, 202 none of this ids exists in A

  So w'll select this row and put the 3 ids in A

  in the end we should have only the first and the fourth row

  I'm looking for a performant T-sql solution without Cursor and iteration because the average amount of row is by millions

  thx

  Unless you put an autonumbering column on this MVTS table, there is no guarantee as to what the order of rows will be which, of course, could change the outcome.

  Also, you've done a great job of explaining what you want done but not the business reason. It may help someone come up with an alternative if they know the business reason. It's also a fairly odd request and knowing the business reason for it would be good "payment" in fair trade for anyone who solves the problem. 🙂 It'll also let us know that it's not homework or a contest on another forum. 😉

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• moadh.bs

  SSC Veteran

  Points: 299

  More actions

  January 19, 2011 at 5:13 am

  #1275212

  You'r right Jeff, I will need an auto-increment column to ensure the order. But let's presume that's the case.

  I tried to simplify the problem because it's a little difficult for me to explain the business reason (especially in English 😉 ). it's an accounting issue where I try to find match between entries to get a picture of a transaction.

  Anyway, I'm at the point of a set of candidate of entry (the columns of MVTS), and at the end I'm gonna tag this entries matched together with a same number. That's why if an entry has been already selected, we have to ignore the others combinations including it.

  this is a part of "Lettrage 1 à 2" mean combining one entry with 2 others.

  I have to be able to go till combining 1 to 4.

• Jeff Moden

  SSC Guru

  Points: 1004339

  More actions

  January 19, 2011 at 5:30 am

  #1275223

  It's an interesting problem because the supposed "set-based" methods that immediately come to mind are all going to be Triangular Joins which will be much slower than any kind of the recursion including a Cursor.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Jeff Moden

  SSC Guru

  Points: 1004339

  More actions

  January 20, 2011 at 8:17 pm

  #1276350

  I've not been able to dedicate much more time thinking about this than I originally did. Did you ever get this problem figured out?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  January 20, 2011 at 11:32 pm

  #1276391

  Probably a self-referencing CTE should do it! Have u tried that?

• moadh.bs

  SSC Veteran

  Points: 299

  More actions

  January 21, 2011 at 1:23 am

  #1276409

  Well the solution If we use a While or a cursor is obvious, I could post this if you want. but I figured out that a hybrid solution .Net & Sql worked best for me, even if we normally prefer coding this kind of process in sql server in order to enhance the maintainability. I'm aware that CTE could perform recursive processing but I'll be curious to see how we could resolve this problem with it.

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  January 22, 2011 at 7:26 am

  #1276895

  Sorry for leading you into a wrong path, i figured out a simple UNPIVOT could do what we need. From the requirement, i am guessing that you need to find the unique values in all the rows and columns into a single column. A DISTINCT coupled with UNPIVOT can do this.

  Here's how:

  SELECT DISTINCT ValInCol

  FROM

  ( SELECT idMvt1,idMvt2,idMvt3 FROM MVTS ) AS PIVOT_TABLE

  UNPIVOT

  ( ValInCol FOR ColName IN (idMvt1,idMvt2,idMvt3) ) AS PIVOT_HANDLE

  I am yet to run this over a million row to find out how this scales. I will do it when time permits 🙂 Meanwhile, if u could u run this piece ( if i had understood the requirement ;-)) over a million rows and post the results, that would be awesome..

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  January 22, 2011 at 7:44 am

  #1276897

  Ok, i had some time to peek into this. Here are the results:

  Test Data:

  USE TempDB

  GO

  SET NOCOUNT ON

  IF OBJECT_ID(N'MVTS',N'U') IS NOT NULL

  DROP TABLE MVTS

  CREATE TABLE MVTS ( idMvt1 int,

  idMvt2 int,

  idMvt3 int);

  INSERT INTO MVTS

  SELECT 271, 204, 136

  UNION ALL

  SELECT 271, 202, 136

  UNION ALL

  SELECT 268, 253, 204

  UNION ALL

  SELECT 268, 253, 202

  UNION ALL

  SELECT 268, 256, 204

  UNION ALL

  SELECT 268, 256, 202

  INSERT INTO MVTS

  SELECT (( ABS(CHECKSUM(NEWID())) % 100) + 200 ) AS idMvt1,

  (( ABS(CHECKSUM(NEWID())) % 100) + 100 ) AS idMvt2,

  (( ABS(CHECKSUM(NEWID())) % 100) + 300 ) AS idMvt3

  FROM dbo.Tally T1,dbo.Tally T2

  WHERE T1.RN < 1001 AND T2.RN < 1001

  Code:

  SELECT DISTINCT ValInCol

  FROM

  ( SELECT idMvt1,idMvt2,idMvt3 FROM MVTS ) AS PIVOT_TABLE

  UNPIVOT

  ( ValInCol FOR ColName IN (idMvt1,idMvt2,idMvt3) ) AS PIVOT_HANDLE

  ORDER BY ValInCol

  Test Harness:

  DECLARE @Bitbucket INT

  SET NOCOUNT ON

  SET STATISTICS TIME ON

  SELECT DISTINCT @Bitbucket = ValInCol

  FROM

  ( SELECT idMvt1,idMvt2,idMvt3 FROM MVTS ) AS PIVOT_TABLE

  UNPIVOT

  ( ValInCol FOR ColName IN (idMvt1,idMvt2,idMvt3) ) AS PIVOT_HANDLE

  ORDER BY ValInCol

  SET STATISTICS TIME OFF

  Results:

  SQL Server Execution Times:

  CPU time = 655 ms, elapsed time = 431 ms.

  SQL Server Execution Times:

  CPU time = 671 ms, elapsed time = 428 ms.

  SQL Server Execution Times:

  CPU time = 607 ms, elapsed time = 422 ms.

  Hope this helps! 😎

• Jeff Moden

  SSC Guru

  Points: 1004339

  More actions

  January 22, 2011 at 9:56 am

  #1276906

  @ColdCoffee,

  It's not just unique values the OP is trying to find. If it was, the OP probably would have been able to do it himself.

  The goal is to find rows where none of the 3 numbers in any given row has been used by any previous row regardless of which position in the row the number appears. Take a look again at the OPs original post. It's not a simple "unique" problem... 😉

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• SwePeso

  SSC-Dedicated

  Points: 39757

  More actions

  January 22, 2011 at 12:11 pm

  #1276917

  How does table A look like?

  ---

  N 56°04'39.16"
  E 12°55'05.25"

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  January 22, 2011 at 6:08 pm

  #1276933

  In the interest of seeing a SQL solution even though you have a CLR solution...

  I offer this

  First just the solution (which assumes you have added an "ID" column)

  SELECT *

  FROM mvts

  WHERE EXISTS (

  SELECT minid

  FROM (

  SELECT VALUE,MIN(id) AS minid

  FROM mvts

  UNPIVOT

  ( VALUE FOR [COLUMN] IN (idMvt1,idMvt2,idMvt3) ) upvt

  GROUP BY VALUE

  ) g

  WHERE minid = id

  GROUP BY minid

  HAVING COUNT(*)=3 )

  ORDER BY id

  And here is the full test bed with some sample results from my PC...

  --= build a test bed

  IF OBJECT_ID('dbo.mvts') IS NULL

  CREATE TABLE mvts (id INT IDENTITY (1, 1) PRIMARY KEY, idMvt1 INT, idMvt2 INT, idMvt3 INT);

  --= build the test data one column at a time

  --= this assumes numbers are less than ten thousand

  --= but can be anything in reality

  --= this way I was sure of duplicates over 1 million rows

  INSERT

  mvts (idMvt1)

  SELECT

  N % 10000

  FROM

  helpers.dbo.Tally AS T -- I have 1M rows in mine.

  ORDER BY

  NEWID ();

  --= randomly populate the second column

  ;WITH cte AS (SELECT TOP 100 PERCENT idMvt2,ROW_NUMBER() OVER (ORDER BY NEWID()) rownum FROM mvts ORDER BY NEWID())

  UPDATE cte

  SET

  idMvt2= rownum % 10000;

  --= randomly populate the third column

  ;WITH cte AS (SELECT TOP 100 PERCENT idMvt3,ROW_NUMBER() OVER (ORDER BY NEWID()) rownum FROM mvts ORDER BY NEWID())

  UPDATE cte

  SET

  idMvt3= rownum % 10000;

  --= remove any rows with duplicate numbers

  DELETE

  FROM

  mvts

  WHERE

  idMvt1 = idMvt2 OR

  idMvt1 = idMvt3 OR

  idMvt2 = idMvt3;

  --= now for the real query

  --= unpivots the columns then groups by number to find the lowest id (the first occurence of each number)

  --= then groups that result by id to find rows that contain three first occurrences

  --= then all that remains is to use that result to pick up the "values" from the idMvt? columns in mvts

  SELECT id, idMvt1, idMvt2, idMvt3

  FROM mvts

  WHERE EXISTS (

  SELECT minid

  FROM (

  SELECT VALUE,MIN(id) AS minid

  FROM mvts

  UNPIVOT

  ( VALUE FOR [COLUMN] IN (idMvt1,idMvt2,idMvt3) ) upvt

  GROUP BY VALUE

  ) g

  WHERE minid = id

  GROUP BY minid

  HAVING COUNT(*)=3 )

  ORDER BY id

  -- sample results for 1M rows with numbers up to 1000

  --(122 row(s) affected)

  --Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  --Table 'mvts'. Scan count 5, logical reads 3819, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  --Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  -- SQL Server Execution Times:

  -- CPU time = 1450 ms, elapsed time = 388 ms.

  -- sample results for 1M rows with numbers up to 10000

  --(1141 row(s) affected)

  --Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  --Table 'mvts'. Scan count 5, logical reads 6835, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  --Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  -- SQL Server Execution Times:

  -- CPU time = 2060 ms, elapsed time = 922 ms.

  -- sample results for 1M rows with numbers up to 100000

  --(11305 row(s) affected)

  --Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  --Table 'mvts'. Scan count 5, logical reads 37327, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  --Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  -- SQL Server Execution Times:

  -- CPU TIME = 5616 ms, elapsed TIME = 3518 ms.

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]