Unique (not distinct) rows in a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Unique (not distinct) rows in a table

Author

Topic

stamford
Starting Member

47 Posts

Posted - 2011-08-05 : 05:26:11

I have five tables named table1 to table5

Each table has a single column named col1 to col5 respectively

The data type for each column is SMALLINT and each column is populated with the same set of data - the numbers 1 to 5

I want to create a table where the rows will be a representation of all the possible combinations of 1 to 5, but they will be unique rather than distinct rows i.e. 12345 and 54321 are distinct in SQL terms but not unique because they contain the same set of numbers, just in a different order.

Subsequently a simple query like the one below will not suffice.

SELECT DISTINCT *
FROM table1, table2, table3, table4, table5

Could someone advise the code that will achieve the required outcome please?

I've pfaffed with this before and I think that the functions

MIN() MAX()

are utilised?

Many thanks

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-08-05 : 06:37:04

I think this is what you are looking for... (but I was a little confused by your question)


Declare @t1 table (c1 smallint)
Declare @t2 table (c2 smallint)
Declare @t3 table (c3 smallint)
Declare @t4 table (c4 smallint)
Declare @t5 table (c5 smallint)

Insert Into @t1 Values (1),(2),(3),(4),(5)
Insert Into @t2 Values (1),(2),(3),(4),(5)
Insert Into @t3 Values (1),(2),(3),(4),(5)
Insert Into @t4 Values (1),(2),(3),(4),(5)
Insert Into @t5 Values (1),(2),(3),(4),(5)


Select * 
From @t1 a
Inner Join @t2 b
On a.c1 <= b.c2
Inner Join @t3 c
On b.c2 <= c.c3
Inner Join @t4 d
On c.c3 <= d.c4
Inner Join @t5 e
On d.c4 <= e.c5

Corey

I Has Returned!!

stamford
Starting Member

47 Posts

Posted - 2011-08-05 : 07:42:16

ok thanks for that. I will run it and get back to you.
Sorry if the question was confusing, I tried to word it as clearly as I could.

quote:

Originally posted by Seventhnight

I think this is what you are looking for... (but I was a little confused by your question)


Declare @t1 table (c1 smallint)
Declare @t2 table (c2 smallint)
Declare @t3 table (c3 smallint)
Declare @t4 table (c4 smallint)
Declare @t5 table (c5 smallint)

Insert Into @t1 Values (1),(2),(3),(4),(5)
Insert Into @t2 Values (1),(2),(3),(4),(5)
Insert Into @t3 Values (1),(2),(3),(4),(5)
Insert Into @t4 Values (1),(2),(3),(4),(5)
Insert Into @t5 Values (1),(2),(3),(4),(5)


Select * 
From @t1 a
Inner Join @t2 b
On a.c1 <= b.c2
Inner Join @t3 c
On b.c2 <= c.c3
Inner Join @t4 d
On c.c3 <= d.c4
Inner Join @t5 e
On d.c4 <= e.c5

Corey

I Has Returned!!

stamford
Starting Member

47 Posts

Posted - 2011-08-05 : 15:55:24

this isn't working exactly as intended - the first row is 1 1 1 1 1, the second is 1 1 1 1 2 and so on, so the element of uniqueness isn't there

quote:
Originally posted by stamford

ok thanks for that. I will run it and get back to you.
Sorry if the question was confusing, I tried to word it as clearly as I could.

quote:
Originally posted by Seventhnight

I think this is what you are looking for... (but I was a little confused by your question)

Declare @t1 table (c1 smallint)
Declare @t2 table (c2 smallint)
Declare @t3 table (c3 smallint)
Declare @t4 table (c4 smallint)
Declare @t5 table (c5 smallint)

Insert Into @t1 Values (1),(2),(3),(4),(5)
Insert Into @t2 Values (1),(2),(3),(4),(5)
Insert Into @t3 Values (1),(2),(3),(4),(5)
Insert Into @t4 Values (1),(2),(3),(4),(5)
Insert Into @t5 Values (1),(2),(3),(4),(5)

Select *
From @t1 a
Inner Join @t2 b
On a.c1 <= b.c2
Inner Join @t3 c
On b.c2 <= c.c3
Inner Join @t4 d
On c.c3 <= d.c4
Inner Join @t5 e
On d.c4 <= e.c5

Corey

I Has Returned!!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-08-05 : 16:03:01

quote:
Originally posted by stamford

this isn't working exactly as intended - the first row is 1 1 1 1 1, the second is 1 1 1 1 2 and so on, so the element of uniqueness isn't there

You'll have to use examples of what you mean by "uniqueness". You mentioned that 12345 and 54321 are not "unique" because they contain the same digits. By that reasoning, there's only 1 "unique" value you can generate from those digits.

stamford
Starting Member

47 Posts

Posted - 2011-08-05 : 18:56:30

the best example i can give is the UK lottery.
there are 49 numbers (1 to 49) and when you buy a lottery ticket it has six numbers - all different.
there are 13,983,816 combinations containing 6 different numbers in the range 1 to 49.
so for instance if you had six tables with one column in each table containing integers 1 to 49, then what code would create a single table containing the 13,983,816 rows of unique combinations?
hope this makes sense.

quote:
Originally posted by robvolk

quote:
Originally posted by stamford

this isn't working exactly as intended - the first row is 1 1 1 1 1, the second is 1 1 1 1 2 and so on, so the element of uniqueness isn't there
You'll have to use examples of what you mean by "uniqueness". You mentioned that 12345 and 54321 are not "unique" because they contain the same digits. By that reasoning, there's only 1 "unique" value you can generate from those digits.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-08-05 : 19:32:38

Take Seventhknight's code and change <= to < for all JOIN conditions.

stamford
Starting Member

47 Posts

Posted - 2011-08-05 : 19:43:22

seventhknight and robvolk - thank you both for taking the time - the code absolutely works now

quote:
Originally posted by robvolk

Take Seventhknight's code and change <= to < for all JOIN conditions.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources