Sort values from multiple columns (1 Viewer)

Krauss

New member
Local time
Today, 11:52
Joined
Oct 31, 2021
Messages
9
Hi,

The problem:

a) Table with n columns (only integers not duplicated)
b) Want to order the multiple values (by row)
c) Create new n columns witch have the values from n columns but sorted

Imagine the euro millions numbers. I have the sequence from draw order and want to have it sorted by its values.

From what I could read, the best approach to this problem is creating an array, sort it and then write it into new columns.
Am I thinking correctly?

Can anyone give me an example how can I go through each line and create an array with what I described?

Sorry for my English.
Regards
Krauss
 

plog

Banishment Pending
Local time
Today, 06:52
Joined
May 11, 2011
Messages
11,613
I think an example would be helpful because words aren't working. Can you demonstrate your issue with data? Provide 2 sets:

A. Starting data from your table. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show us what you expect to end up with when you feed in the data from A.

Again, 2 sets of data to demonstrate the issue---starting and ending.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:52
Joined
Feb 19, 2013
Messages
16,555
like euro millions? provide an example of required result since from your description, just highlight the columns and sort - columns need to be in the order required So data of

1...2
2...4
4....3
2....1
1...6

would be sorted

1...2
1...6
2...1
2...4
4...3

don't see why you need new columns
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:52
Joined
Feb 19, 2002
Messages
42,981
Welcome aboard Krauss:)
It would have been better to give us a sample of the input data and a sample of what you want. I'm guessing that you are trying to calculate the frequency of the numbers drawn for a lottery. Because the table is not normalized, the numbers picked for each drawing are a repeating group. So you have six columns, one for each number picked and each column has a value of 1-n excluding any numbers that were already picked for this drawing.

Tables are arrays so rather than making another one, use queries to examine the big array you already have. You need six queries to count the values in each of the columns
Select Col1 as FieldValue, Count(*) As ValueCount From YourTable Group by FieldValue;
Select Col2 as FieldValue, Count(*) As ValueCount From YourTable Group by FieldValue;
Select Col3 as FieldValue, Count(*) As ValueCount Group by FieldValue;
etc. From YourTable
This gives you six sets of data that you now need to get into a single query so the easiest way is with a union So you could do this:
Select Col1 as FieldValue, Count(*) As ValueCount From YourTable Group by FieldValue
Union ALL Select Col2 as FieldValue, Count(*) As ValueCount From YourTable Group by FieldValue
Union ALL Select Col3 as FieldValue, Count(*) As ValueCount From YourTable Group by FieldValue
...
Then you need a queyr to sum the values in the union
Select FieldValues, Sum(ValueCount0 From YourTable Group by Group by FieldValue;

The analysis would be much simpler if the table were:

DwgDT
ColumnNum
ValueDrawn

Then you could do the whole thing with this query:

Select ValueDrawn, Count(*) As ValueCount From YourTable;

What a difference normalization makes:)
 

Krauss

New member
Local time
Today, 11:52
Joined
Oct 31, 2021
Messages
9
FIRST: Wow, what a community! After 20 minutes 3 reply trying to help! Thank you.

An example of what I'm trying to do:

What I have:

ID; N1; N2; N3; N4
1; 10; 20; 15; 9
2; 2; 44; 5; 9
3; 7; 38; 25; 28

What I want:

ID; N1; N2; N3; N4; N11; N21; N31; N41
1; 10; 20; 15; 9; 9; 10; 15; 20
2; 2; 44; 5; 9; 2; 5; 9; 44
3; 7; 38; 25; 28; 7; 25; 28; 38

Probably I didn't express myself correctly. But I think the example shows What I mean.

Once again, hank you all for your generosity!


Regards
Krauss
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:52
Joined
Oct 29, 2018
Messages
21,358
FIRST: Wow, what a community! After 20 minutes 3 reply trying to help! Thank you.

An example of what I'm trying to do:

What I have:

ID; N1; N2; N3; N4
1; 10; 20; 15; 9
2; 2; 44; 5; 9
3; 7; 38; 25; 28

What I want:

ID; N1; N2; N3; N4; N11; N21; N31; N41
1; 10; 20; 15; 9; 9; 10; 15; 20
2; 2; 44; 5; 9; 2; 5; 9; 44
3; 7; 38; 25; 28; 7; 25; 28; 38

Probably I didn't express myself correctly. But I think the example shows What I mean.

Once again, hank you all for your generosity!


Regards
Krauss
That might require using VBA and a temporary table, and it will probably be a slow process. How many records and columns are we talking about?
 

plog

Banishment Pending
Local time
Today, 06:52
Joined
May 11, 2011
Messages
11,613
You haven't constructed your table properly. Tables should be built to accomodate data vertically (with more rows) and not horizontally (with more columns). Instead of 9 columns to hold all this data, you just need 3 columns and then a query to get the results you want.

1 of those fields will be a "Group" field. You're data implicitly has that grouping idea, but not an actual field for it. Right now, every row is a group in your current structure. N1, N2, N3 and N4 are a group of numbers. In this new structure we are going to explicitly assign a number to every group. This is what your table should look like:

tblNumbers
num_Group, number, used to group sets of numbers together
num_Value, number, actual value of the number currently in the N1-N4 fields
num_Position, number, this will determine the beginning order of your numbers

So, for ID=1 & ID=2 in your current data would look like this in your the tblNumbers:

num_Group, num_Value, num_Position
1, 10, 1
1, 20, 2
1, 15, 3
1, 9, 4
2, 2, 1
2, 44, 2
2, 5, 3
2, 9, 4

Now, to sort them its trivial you just run a query, sort by num_Group then by num_Position and everything is in the order you want.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:52
Joined
Feb 19, 2013
Messages
16,555
Ok so next problem is the data is not normalised and your example shows 4 number columns - but in your first post says 'table with n columns'

Think this is really a problem for excel to resolve rather than a database
 

Krauss

New member
Local time
Today, 11:52
Joined
Oct 31, 2021
Messages
9
That might require using VBA and a temporary table, and it will probably be a slow process. How many records and columns are we talking about?
A few thousands, time it-s not a problem it's a self-taught project/discovery
 

Krauss

New member
Local time
Today, 11:52
Joined
Oct 31, 2021
Messages
9
Ok so next problem is the data is not normalised and your example shows 4 number columns - but in your first post says 'table with n columns'
You're right! My "n", would be the number of columns that I want to sort even if I have a bunch of others like an ID.
 

Krauss

New member
Local time
Today, 11:52
Joined
Oct 31, 2021
Messages
9
You haven't constructed your table properly. Tables should be built to accomodate data vertically (with more rows) and not horizontally (with more columns). Instead of 9 columns to hold all this data, you just need 3 columns and then a query to get the results you want.

1 of those fields will be a "Group" field. You're data implicitly has that grouping idea, but not an actual field for it. Right now, every row is a group in your current structure. N1, N2, N3 and N4 are a group of numbers. In this new structure we are going to explicitly assign a number to every group. This is what your table should look like:

tblNumbers
num_Group, number, used to group sets of numbers together
num_Value, number, actual value of the number currently in the N1-N4 fields
num_Position, number, this will determine the beginning order of your numbers

So, for ID=1 & ID=2 in your current data would look like this in your the tblNumbers:

num_Group, num_Value, num_Position
1, 10, 1
1, 20, 2
1, 15, 3
1, 9, 4
2, 2, 1
2, 44, 2
2, 5, 3
2, 9, 4

Now, to sort them its trivial you just run a query, sort by num_Group then by num_Position and everything is in the order you want.

So elegant!!!!

So, the problem now, is create an index for each number and then create another index after sort the values.

PS: I have more columns to work (will try to do a ML model)
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:52
Joined
Feb 19, 2013
Messages
16,555
my question was the same as dbg - how many columns. I probably wasn't clear enough

And if this is euro lottery, don't forget the lucky numbers are on a different count (so 1 can be in the main draw and drawn again for the lucky numbers)
 

Krauss

New member
Local time
Today, 11:52
Joined
Oct 31, 2021
Messages
9
my question was the same as dbg - how many columns. I probably wasn't clear enough

And if this is euro lottery, don't forget the lucky numbers are on a different count (so 1 can be in the main draw and drawn again for the lucky numbers)
It's a multidimensional problem, several columns (features). The reason I want to have some columns sorted it's because I want to calculate the distance (minimum) between some features, a kind of PCA (principal components analysis), probably something you all are used to work with.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:52
Joined
Feb 19, 2013
Messages
16,555
@MajP has experience of this sort of thing - may be able to help. Including his name should notify him of this thread
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:52
Joined
May 21, 2018
Messages
8,463
It's a multidimensional problem, several columns (features). The reason I want to have some columns sorted it's because I want to calculate the distance (minimum) between some features, a kind of PCA (principal components analysis), probably something you all are used to work with.
Can you explain the whole process because I assume a sorted solution of columns is just a part of it.
Does your data always have N data points? My guess is if this is financial data and for PCA analysis you could have any dimension.
I would expect real data to be as described by @plog to allow you to handle any N-tuple of data.

I am not very smart in this area. From what I do know you have several steps
1. Standardize the data
2. Compute the Covariance Matrix
3. Find eigen values and Eigen Vectors
4. Recast the data along the principal axis determined in step 3

If your desire is to do this, then I do not know if you need to resave the ordering in the table. I think this is done outside the table and any temp table. Only the results are written back. If you do what @plog showed, it is automatic to sort on the column, and still retain the original position.

If you already have data that comes as a 4-tuple in the format above, you can get it into the format described by @plog using a union query. At some large N writing the union query will be a PITA.

Without seeing the real format of the input and the desired analysis, I am only guessing at what you want. If I was doing the above PCA. I would build a class module that I could pass in a recordset representing a group of N-tuples in the format that @plog described. The class would build the covariance Matrix, and Eigen Vectors. With that said I probably also have 2 small classes Eigen Vector, and Covariance Matrix. Another small collection class EigenVectors. In the final step you have to transpose the original data set and do matrix multiplication with the transpose of the feature vector. With that said you probably do not want to do this in the recordset or temp table. You then need a generic Matrix class. Where you can pass in the recordset. Get an M x N and then be able to transpose to an N x M.

You can do this all above in a single standard module but for me it is just more logical in a classes.
 

Krauss

New member
Local time
Today, 11:52
Joined
Oct 31, 2021
Messages
9
First of all, I appreciate your dedication in trying to help.

What I intend to do is pre-process data to analyze behavior. Imagine that these are balance values in which it is necessary to understand if it is possible to foresee the most usual balances for each person (with margins, of course). One of the steps is to get the smallest differences between the positions. This informs us of the shortest path between all the positions that happened(a kind of traveling salesman problem). I admit that this may not make much sense but understand that I can't go much further. This is not PCA! The only similarity with PCA would be the relationship between variables and the reduction of dimensionality of the problem. But it was the way I found to explain, so that I could show that a situation like this can be useful. Normally, this type of task is performed in Python I was just practicing and exploring other ways as it can come to be useful depending on the various problems and file types that appear to us.

In short:
I have a dataset with numerous features and I intend to reorder the values of some of them (this is decisive because it will force you to create a temporary dataset if you don't do it in VBA) so that I can calculate the distances between them (the smallest). If I can do it with VBA, I think it would be enough to create an array with the values, sort them and write again in columns created for this purpose. Then I could easily calculate the distances because the columns between which I would have to perform the calculations would always be the same for all cases since the values would be sorted. (in each row).
I didn't remember any case more visual than the euromillions. It's not a good example, as its usefulness is not intuitive, but it conveys exactly what I intend to do.

Thank you for your patience! 😌

Best regards
Krauss
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:52
Joined
Feb 19, 2013
Messages
16,555
One of the steps is to get the smallest differences between the positions

with normalised data that is simple to achieve

using plogs example data, join the table to itself on numgroup

Code:
SELECT A.numgroup, min(abs(A.numvalue-B.numvalue)) as shortest
FROM myTable A INNER JOIN myTable B ON A.numgroup=B.numgroup
WHERE A.numvalue<>B.numvalue
GROUP BY A.numgroup
 

Krauss

New member
Local time
Today, 11:52
Joined
Oct 31, 2021
Messages
9
I don't know if what I'm going to say will seem weird, crazy or something else but the feeling I get when I see a solution like this is "elegant".

It's that moment when intelligence is recognized and it doesn't need to be something complex.
Simplification is often the ultimate intelligence, once done, the rest becomes a consequence. (my quote)

I'm sorry if I went beyond the purpose of the forum, thanks for the help!
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:52
Joined
Feb 19, 2013
Messages
16,555
the trick is to think 'database', not 'excel'. For databases to work effectively (and elegantly!), the data needs to be normalised.
 

Users who are viewing this thread

Top Bottom