July 22, 2011 at 6:33 am
Kath Smith (7/22/2011)
Don't forget that they way the data is pulled could change any given pattern when there are columns before the search column that may order by a key field. You will need to make sure that all data within the table you are searching if always ordered by the same criteria.Your example with only a identity and a number is not realistic of any given table that any given data table would contain.
You know this thing gets bigger the more i dive into it. At first I was looking into only one of the columns for a pattern. Then I thought about it and it has depending on the table 3-6 numeric/int columns. Then it I said what if the pattern could also be horizontal and not just vertical? That threw another gallon of gasoline on the fire! Anyway I have come up with several patterns I started with: are the numbers even or odd. The even/odd thing allows me to understand a pattern which does not always jump out ex. even odd even, odd odd even, or odd even odd. Those patterns make it easier for your head to absorb.
July 22, 2011 at 9:33 am
For anyone who really wants to get there hands dirty here is a data set to help:
--create table
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Chann4_1]') AND type in (N'U'))
DROP TABLE [dbo].[Chann4_1]
GO
CREATE TABLE [dbo].[Chann4_1](
[id] [int] IDENTITY(1,1) NOT NULL,
[Num1] [int] NULL,
[Num2] [int] NULL,
[Num3] [int] NULL,
[Num4] [int] NULL,
[Numbs] [int] NULL
) ON [PRIMARY]
GO;
--insert some data
INSERT INTO [Test].[dbo].[Chann4_1]('1','5' ,'5' ,'8' ,'6' ,'5586');
INSERT INTO [Test].[dbo].[Chann4_1]('2','6' ,'1' ,'2' ,'8' ,'6128');
INSERT INTO [Test].[dbo].[Chann4_1]('3','2' ,'3' ,'1' ,'9' ,'2319');
INSERT INTO [Test].[dbo].[Chann4_1]('4','6' ,'4' ,'4' ,'4' ,'6444');
INSERT INTO [Test].[dbo].[Chann4_1]('5','6' ,'0' ,'4' ,'7' ,'6047');
INSERT INTO [Test].[dbo].[Chann4_1]('6','7' ,'2' ,'9' ,'7' ,'7297');
INSERT INTO [Test].[dbo].[Chann4_1]('7','6' ,'6' ,'7' ,'3' ,'6673');
INSERT INTO [Test].[dbo].[Chann4_1]('8','7' ,'6' ,'3' ,'2' ,'7632');
INSERT INTO [Test].[dbo].[Chann4_1]('9','8' ,'4' ,'1' ,'0' ,'8410');
INSERT INTO [Test].[dbo].[Chann4_1]('10','1' ,'3' ,'4' ,'1' ,'1341');
INSERT INTO [Test].[dbo].[Chann4_1]('11','3' ,'0' ,'7' ,'7' ,'3077');
INSERT INTO [Test].[dbo].[Chann4_1]('12','6' ,'5' ,'9' ,'2' ,'6592');
INSERT INTO [Test].[dbo].[Chann4_1]('13','3' ,'3' ,'6' ,'6' ,'3366');
INSERT INTO [Test].[dbo].[Chann4_1]('14','5' ,'6' ,'2' ,'3' ,'5623');
INSERT INTO [Test].[dbo].[Chann4_1]('15','9' ,'5' ,'6' ,'2' ,'9562');
INSERT INTO [Test].[dbo].[Chann4_1]('16','2' ,'1' ,'2' ,'6' ,'2126');
INSERT INTO [Test].[dbo].[Chann4_1]('17','6' ,'8' ,'4' ,'9' ,'6849');
INSERT INTO [Test].[dbo].[Chann4_1]('18','6' ,'8' ,'8' ,'4' ,'6884');
INSERT INTO [Test].[dbo].[Chann4_1]('19','5' ,'8' ,'5' ,'7' ,'5857');
INSERT INTO [Test].[dbo].[Chann4_1]('20','3' ,'3' ,'3' ,'5' ,'3335');
INSERT INTO [Test].[dbo].[Chann4_1]('21','4' ,'4' ,'5' ,'4' ,'4454');
INSERT INTO [Test].[dbo].[Chann4_1]('22','0' ,'6' ,'8' ,'4' ,'684');
INSERT INTO [Test].[dbo].[Chann4_1]('23','0' ,'6' ,'9' ,'2' ,'692');
INSERT INTO [Test].[dbo].[Chann4_1]('24','7' ,'0' ,'0' ,'0' ,'7000');
INSERT INTO [Test].[dbo].[Chann4_1]('25','5' ,'3' ,'0' ,'4' ,'5304');
INSERT INTO [Test].[dbo].[Chann4_1]('26','1' ,'6' ,'3' ,'7' ,'1637');
INSERT INTO [Test].[dbo].[Chann4_1]('27','8' ,'1' ,'5' ,'3' ,'8153');
INSERT INTO [Test].[dbo].[Chann4_1]('28','7' ,'8' ,'5' ,'3' ,'7853');
INSERT INTO [Test].[dbo].[Chann4_1]('29','2' ,'2' ,'4' ,'6' ,'2246');
INSERT INTO [Test].[dbo].[Chann4_1]('30','5' ,'9' ,'8' ,'4' ,'5984');
INSERT INTO [Test].[dbo].[Chann4_1]('31','0' ,'2' ,'6' ,'6' ,'266');
INSERT INTO [Test].[dbo].[Chann4_1]('32','9' ,'8' ,'1' ,'2' ,'9812');
INSERT INTO [Test].[dbo].[Chann4_1]('33','2' ,'0' ,'0' ,'6' ,'2006');
INSERT INTO [Test].[dbo].[Chann4_1]('34','0' ,'0' ,'9' ,'6' ,'96');
INSERT INTO [Test].[dbo].[Chann4_1]('35','4' ,'6' ,'7' ,'3' ,'4673');
INSERT INTO [Test].[dbo].[Chann4_1]('36','4' ,'7' ,'6' ,'9' ,'4769');
INSERT INTO [Test].[dbo].[Chann4_1]('37','6' ,'6' ,'2' ,'6' ,'6626');
INSERT INTO [Test].[dbo].[Chann4_1]('38','5' ,'8' ,'8' ,'9' ,'5889');
INSERT INTO [Test].[dbo].[Chann4_1]('39','8' ,'3' ,'1' ,'0' ,'8310');
INSERT INTO [Test].[dbo].[Chann4_1]('40','0' ,'7' ,'0' ,'6' ,'706');
INSERT INTO [Test].[dbo].[Chann4_1]('41','6' ,'9' ,'6' ,'5' ,'6965');
INSERT INTO [Test].[dbo].[Chann4_1]('42','3' ,'7' ,'5' ,'1' ,'3751');
INSERT INTO [Test].[dbo].[Chann4_1]('43','6' ,'3' ,'4' ,'4' ,'6344');
INSERT INTO [Test].[dbo].[Chann4_1]('44','8' ,'7' ,'3' ,'0' ,'8730');
INSERT INTO [Test].[dbo].[Chann4_1]('45','8' ,'9' ,'2' ,'5' ,'8925');
INSERT INTO [Test].[dbo].[Chann4_1]('46','2' ,'7' ,'8' ,'1' ,'2781');
INSERT INTO [Test].[dbo].[Chann4_1]('47','3' ,'5' ,'0' ,'4' ,'3504');
INSERT INTO [Test].[dbo].[Chann4_1]('48','0' ,'4' ,'0' ,'3' ,'403');
INSERT INTO [Test].[dbo].[Chann4_1]('49','1' ,'4' ,'0' ,'3' ,'1403');
INSERT INTO [Test].[dbo].[Chann4_1]('50','6' ,'1' ,'1' ,'8' ,'6118');
INSERT INTO [Test].[dbo].[Chann4_1]('51','4' ,'3' ,'4' ,'2' ,'4342');
INSERT INTO [Test].[dbo].[Chann4_1]('52','5' ,'9' ,'7' ,'2' ,'5972');
INSERT INTO [Test].[dbo].[Chann4_1]('53','5' ,'7' ,'9' ,'6' ,'5796');
INSERT INTO [Test].[dbo].[Chann4_1]('54','8' ,'8' ,'5' ,'4' ,'8854');
INSERT INTO [Test].[dbo].[Chann4_1]('55','8' ,'5' ,'3' ,'8' ,'8538');
INSERT INTO [Test].[dbo].[Chann4_1]('56','4' ,'9' ,'3' ,'7' ,'4937');
INSERT INTO [Test].[dbo].[Chann4_1]('57','9' ,'6' ,'3' ,'3' ,'9633');
INSERT INTO [Test].[dbo].[Chann4_1]('58','4' ,'4' ,'0' ,'8' ,'4408');
INSERT INTO [Test].[dbo].[Chann4_1]('59','9' ,'3' ,'7' ,'8' ,'9378');
INSERT INTO [Test].[dbo].[Chann4_1]('60','1' ,'9' ,'2' ,'3' ,'1923');
INSERT INTO [Test].[dbo].[Chann4_1]('61','2' ,'7' ,'8' ,'3' ,'2783');
INSERT INTO [Test].[dbo].[Chann4_1]('62','3' ,'6' ,'1' ,'4' ,'3614');
INSERT INTO [Test].[dbo].[Chann4_1]('63','6' ,'8' ,'8' ,'4' ,'6884');
INSERT INTO [Test].[dbo].[Chann4_1]('64','7' ,'2' ,'4' ,'1' ,'7241');
INSERT INTO [Test].[dbo].[Chann4_1]('65','0' ,'5' ,'3' ,'7' ,'537');
INSERT INTO [Test].[dbo].[Chann4_1]('66','1' ,'0' ,'1' ,'5' ,'1015');
INSERT INTO [Test].[dbo].[Chann4_1]('67','3' ,'3' ,'6' ,'0' ,'3360');
INSERT INTO [Test].[dbo].[Chann4_1]('68','0' ,'5' ,'3' ,'4' ,'534');
INSERT INTO [Test].[dbo].[Chann4_1]('69','7' ,'8' ,'5' ,'4' ,'7854');
INSERT INTO [Test].[dbo].[Chann4_1]('70','3' ,'9' ,'2' ,'8' ,'3928');
INSERT INTO [Test].[dbo].[Chann4_1]('71','4' ,'5' ,'8' ,'5' ,'4585');
INSERT INTO [Test].[dbo].[Chann4_1]('72','1' ,'9' ,'6' ,'5' ,'1965');
INSERT INTO [Test].[dbo].[Chann4_1]('73','9' ,'0' ,'0' ,'7' ,'9007');
INSERT INTO [Test].[dbo].[Chann4_1]('74','7' ,'6' ,'5' ,'0' ,'7650');
INSERT INTO [Test].[dbo].[Chann4_1]('75','6' ,'2' ,'0' ,'5' ,'6205');
INSERT INTO [Test].[dbo].[Chann4_1]('76','7' ,'1' ,'4' ,'5' ,'7145');
INSERT INTO [Test].[dbo].[Chann4_1]('77','5' ,'6' ,'3' ,'1' ,'5631');
INSERT INTO [Test].[dbo].[Chann4_1]('78','2' ,'2' ,'7' ,'3' ,'2273');
INSERT INTO [Test].[dbo].[Chann4_1]('79','1' ,'0' ,'3' ,'0' ,'1030');
INSERT INTO [Test].[dbo].[Chann4_1]('80','2' ,'7' ,'5' ,'4' ,'2754');
INSERT INTO [Test].[dbo].[Chann4_1]('81','9' ,'6' ,'2' ,'0' ,'9620');
INSERT INTO [Test].[dbo].[Chann4_1]('82','4' ,'7' ,'0' ,'6' ,'4706');
INSERT INTO [Test].[dbo].[Chann4_1]('83','4' ,'0' ,'2' ,'3' ,'4023');
INSERT INTO [Test].[dbo].[Chann4_1]('84','5' ,'1' ,'7' ,'3' ,'5173');
INSERT INTO [Test].[dbo].[Chann4_1]('85','0' ,'5' ,'9' ,'2' ,'592');
INSERT INTO [Test].[dbo].[Chann4_1]('86','3' ,'8' ,'1' ,'0' ,'3810');
INSERT INTO [Test].[dbo].[Chann4_1]('87','4' ,'5' ,'3' ,'5' ,'4535');
INSERT INTO [Test].[dbo].[Chann4_1]('88','3' ,'1' ,'4' ,'5' ,'3145');
INSERT INTO [Test].[dbo].[Chann4_1]('89','0' ,'3' ,'4' ,'5' ,'345');
INSERT INTO [Test].[dbo].[Chann4_1]('90','4' ,'6' ,'9' ,'7' ,'4697');
INSERT INTO [Test].[dbo].[Chann4_1]('91','8' ,'5' ,'5' ,'7' ,'8557');
INSERT INTO [Test].[dbo].[Chann4_1]('92','6' ,'2' ,'3' ,'3' ,'6233');
INSERT INTO [Test].[dbo].[Chann4_1]('93','9' ,'0' ,'9' ,'8' ,'9098');
INSERT INTO [Test].[dbo].[Chann4_1]('94','8' ,'5' ,'8' ,'4' ,'8584');
INSERT INTO [Test].[dbo].[Chann4_1]('95','6' ,'5' ,'3' ,'9' ,'6539');
INSERT INTO [Test].[dbo].[Chann4_1]('96','5' ,'5' ,'7' ,'1' ,'5571');
INSERT INTO [Test].[dbo].[Chann4_1]('97','4' ,'7' ,'3' ,'2' ,'4732');
INSERT INTO [Test].[dbo].[Chann4_1]('98','0' ,'2' ,'1' ,'3' ,'213');
INSERT INTO [Test].[dbo].[Chann4_1]('99','3' ,'8' ,'4' ,'0' ,'3840');
INSERT INTO [Test].[dbo].[Chann4_1]('100','2' ,'6' ,'5' ,'1' ,'2651');
-- one of the pattern selects I have found
SELECT TOP (100) PERCENT COUNT(it) AS NumTimesRun, it AS [Order]
FROM (SELECT TOP (1000) Num1, Num2, Num3, Num4, CASE WHEN ABS(Num1) % 2 = 1 THEN 'odd' ELSE 'even' END + CASE WHEN ABS(Num2) % 2 = 1 THEN 'odd' ELSE 'even' END + CASE WHEN ABS(Num3) % 2 = 1 THEN 'odd' ELSE 'even' END + CASE WHEN ABS(Num4) % 2 = 1 THEN 'odd' ELSE 'even' END AS it FROM dbo.Chann4_1) AS a
GROUP BY it
ORDER BY NumTimesRun
So here you go if you wanted a real test case to give some help by.(it took me a little while to get it together.)
July 22, 2011 at 10:51 am
No problem. Just UNPIVOT the data and use my suggestion earlier.
N 56°04'39.16"
E 12°55'05.25"
July 22, 2011 at 10:56 am
SwePeso (7/22/2011)
No problem. Just UNPIVOT the data and use my suggestion earlier.
WOW! You saw a pivot in my post? No pivot in my post only suggestions from others. My last post gave a lot more information for you to help me with if you want. I also said some of the patterns that I found so far are even and odd patterns. So thanks.
Viewing 4 posts - 31 through 33 (of 33 total)
You must be logged in to reply to this topic. Login to reply