Smart questions
Smart people
 Find A ForumFind An Expert
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Remember Me

Are you a
Computer / IT professional?
Join Tek-Tips now!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Just copy and paste the

#### Feedback

"...On your site I feel quite confident that the contacts and feedback will make my life a little less hectic..."

#### Geography

Where in the world do Tek-Tips members come from?

# Help to identify sets of distinct unordered values

 Forum Search FAQs Links Jobs Whitepapers MVPs
 teach314 (TechnicalUser) 17 Jul 12 22:04
 hi - I am struggling to do the following: consider a table like the one below. The actual table has 12 columns and over 900 rows. The values in each row have no 'doubles'. ID F1 F2 F3 F4 F5 F6 1 12 15 8 19 34 21 2 18 10 29 11 77 9 3 34 8 12 15 21 19 4 8 15 19 21 34 12 5 1 12 54 45 99 33 6 1 33 12 99 45 54 I want to find rows that have a distinct set of the 6 values, regardless of order. In the example given, rows 1, 3 and 4 all have the same 6 values. Similarly, rows 5 and 6 share the same 6 values. Row 2 has no matches. Ideally, the output would show the values in each ROW descending from LEFT to RIGHT. Also, it would be nice if the ID column showed the first occurrence for each distinct set of 6 values. (as shown below) ID T1 T2 T3 T4 T5 T6 1 34 21 19 15 12 8 2 77 29 18 11 10 9 5 99 54 45 33 12 1 many thanks for any help. I realize that the tables aren't in normalized form, but I could convert them if it would help solve the problem.
 dhookom (Programmer) 18 Jul 12 1:28
I would first create a union query [TT_quniTeach314] like:

#### CODE --> SQL

```SELECT ID, F1 AS TheVal, 1 AS Fld
FROM TT_Teach314
UNION ALL
SELECT ID, F2, 2
FROM TT_Teach314
UNION ALL
SELECT ID, F3, 3
FROM TT_Teach314
UNION ALL
SELECT ID, F4, 4
FROM TT_Teach314
UNION ALL
SELECT ID, F5, 5
FROM TT_Teach314
UNION ALL SELECT ID, F6, 6
FROM TT_Teach314; ```

Then create a ranking query [TT_qrnkTeach314] to order the records for each ID:

#### CODE --> SQL

```SELECT TT_quniTeach314.ID, TT_quniTeach314.TheVal, Count(TT_quniTeach314_1.ID) AS CountOfID
FROM TT_quniTeach314 AS TT_quniTeach314_1 INNER JOIN TT_quniTeach314
ON TT_quniTeach314_1.ID = TT_quniTeach314.ID
WHERE (((TT_quniTeach314.TheVal)<=[TT_quniTeach314_1].[TheVal]))
GROUP BY TT_quniTeach314.ID, TT_quniTeach314.TheVal
ORDER BY TT_quniTeach314.ID, Count(TT_quniTeach314_1.ID); ```
Then create a crosstab query [TT_qxtbTeach314]

#### CODE --> SQL

```TRANSFORM Min(TT_qrnkTeach314.TheVal) AS MinOfTheVal
SELECT TT_qrnkTeach314.ID
FROM TT_qrnkTeach314
GROUP BY TT_qrnkTeach314.ID
PIVOT "T" & [CountOfID]; ```

Finally create a totals query with SQL of:

#### CODE --> SQL

```SELECT Min(TT_qxtbTeach314.ID) AS MinOfID, TT_qxtbTeach314.T1, TT_qxtbTeach314.T2,
TT_qxtbTeach314.T3, TT_qxtbTeach314.T4, TT_qxtbTeach314.T5, TT_qxtbTeach314.T6
FROM TT_qxtbTeach314
GROUP BY TT_qxtbTeach314.T1, TT_qxtbTeach314.T2, TT_qxtbTeach314.T3, TT_qxtbTeach314.T4,
TT_qxtbTeach314.T5, TT_qxtbTeach314.T6; ```
 teach314 (TechnicalUser) 18 Jul 12 18:05
hey dhookum, that is a really thorough and educational solution. I tried it on my large tables and things everything worked without a hitch. much thanks for your time.

I'm still new enough to SQL that I really have to fight the urge to see data in non-normalized 'spreadsheet' type tables. But I see that you started by using a UNION query to normalize the data, did the needed work, then used a crosstab query to put things back into 'spreadsheet' format.

I have a quick question. after your ranking query, the data looks like this:

#### CODE

```ID	TheVal	CountOfID
1	34	1
1	21	2
1	19	3
1	15	4
1	12	5
1	8	6
2	77	1
2	29	2
2	18	3
2	11	4
2	10	5
2	9	6
3	34	1
3	21	2 ```
... etc...36 rows in total

so, if I forget about using the crosstab qry to convert back to 'spreadsheet' format, what is the best way to show all DISTINCT sets of TheVal while still in normalized form? The output would look exactly like above, but showing only rows having ID = 1, 2 or 5 as shown in my original posting. (18 rows in total)

TQ,teach314

 dhookom (Programmer) 18 Jul 12 18:26
 You could take the final query and join it to the normalizing union query.
 teach314 (TechnicalUser) 18 Jul 12 22:23
 I think you must mean "... and join it to the ranking query". In any case, I'm not yet able to get this to join properly to the final query. I'll keep trying.
 dhookom (Programmer) 18 Jul 12 22:55
 I would join to the MinOfID to either the union query or the ranking query.
 teach314 (TechnicalUser) 18 Jul 12 23:30
 ok, thanks dhookum - your suggestion works well (setting ID of ranking query to MinOfID in the final query.) The only thing that seems odd about doing it this way was that the final query invokes a non-normalized crosstab query. I guess I was hoping we could get the final results in normalized form from either the ranking query or the union query, both in normalized form, without having to use the crosstab query at all. many thanks for your help so far!

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!