Looking For A Better Way

[Wayne Wengert]

I have a VB VSE 2005 project in which I need to rank about 35 columns of
numbers. I want to end up with a dataset of ordinals. For example, if I have
a dataset with 5 rows in which colA values are 6.6, 6.5, 6.2, 6.6, 6.7
respectively, the resulting ordinals would be 2, 4, 5, 2, 1.

I am currntly doing this in a brute force way. I put the values in an array
and then walk through each column to calculate the ordinal and place it in a
second array. When I have all the ordinals I put the array data into a
dataset (I need a dataset to pass to the reporting code). Is there a better
way to accomplish this?

TIA

Wayne

 

[Mr Newbie]

Perhaps a list would do what you need. Each list item would contain a value
( countValue ), this way you could do something like

PSEUDO CODE
For Each Value in your collection
If List contains key, then Add 1 to value else create new Item with
value of 1
End Loop

Bubble Sort List.

 

[Ken Tucker [MVP]]

Hi,

If you are getting the data from a sql server 2005 database you
can use the row_number() over (order by colA) to create the ordinal. Here is
a simple example.

Dim strConn As String
Dim da As SqlDataAdapter
Dim conn As SqlConnection
Dim ds As New DataSet
strConn = "Server = (local)\SQLEXPRESS;"
strConn &= "Database = NorthWind; Integrated Security = SSPI;"
conn = New SqlConnection(strConn)

da = New SqlDataAdapter("Select Row_Number() Over(Order by
CustomerID) as MyOrdinal, * From Customers", conn)
da.Fill(ds, "Customers")

DataGridView1.DataSource = ds.Tables("Customers")


Ken

 

[Wayne Wengert]

Thanks for the response. I am not sure exactly what you are suggesting here?
In the pseudo code, what is "key" in "If list contains key.."

Wayne

 

[Mr Newbie]

Thats a really neat way of doing it using SQL Server.

Very impressive.

 

[Mr Newbie]

Well, in a list of items, the key is the reference and the value is what is
assigned to it, basically I am talking about a value/pair list.

 

[Guest]

I have a VB VSE 2005 project in which I need to rank about 35 columns of

numbers. I want to end up with a dataset of ordinals. For example, if I have
a dataset with 5 rows in which colA values are 6.6, 6.5, 6.2, 6.6, 6.7
respectively, the resulting ordinals would be 2, 4, 5, 2, 1.

You get part way there using Array.Sort with a tag-along vector:

Dim a() As Double = {6.6, 6.5, 6.2, 6.6, 6.7}
Dim n As Integer = UBound(a)
Dim r(n) As Integer
Dim i As Integer
For i = 0 To n : r(i) = i : Next
Array.Sort(a, r) ' sort a() ascending and tag along r()
Dim ord(n) As Integer
' below, n-i for descending vice ascending,
' and +1 for numbering starting at 1 vice 0
For i = 0 To n : ord(r(i)) = n - i + 1 : Next ' 2,4,5,3,1

If you want 2,4,5,2,1 vice 2,4,5,3,1, then some work remains where there
were ties in the data (6.6 in your example).

 

[Wayne Wengert]

Thanks Ken;

I am not using SQL server in this case but that is a very neat approach.
I'll have to experiment with it.

Wayne