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]

Re: Looking For A Better Way

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.






"Wayne Wengert" <wayneSKIPSPAM@ wengert.org> wrote in message
news:ul$$WImyFH A.2348@TK2MSFTN GP15.phx.gbl...[color=blue]
>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
>[/color]

[Ken Tucker [MVP]]

Re: Looking For A Better Way

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)\SQLEXPR ESS;"
strConn &= "Database = NorthWind; Integrated Security = SSPI;"
conn = New SqlConnection(s trConn)

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

DataGridView1.D ataSource = ds.Tables("Cust omers")


Ken
----------------------------------------
"Wayne Wengert" <wayneSKIPSPAM@ wengert.org> wrote in message
news:ul$$WImyFH A.2348@TK2MSFTN GP15.phx.gbl...[color=blue]
>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
>[/color]

[Wayne Wengert]

Re: Looking For A Better Way

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" <here@now.com > wrote in message
news:uQzP7RmyFH A.2812@TK2MSFTN GP14.phx.gbl...[color=blue]
> 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.
>
>
>
>
>
>
> "Wayne Wengert" <wayneSKIPSPAM@ wengert.org> wrote in message
> news:ul$$WImyFH A.2348@TK2MSFTN GP15.phx.gbl...[color=green]
>>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
>>[/color]
>
>[/color]

[Mr Newbie]

Re: Looking For A Better Way

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

Very impressive.

"Ken Tucker [MVP]" <vb2ae@bellsout h.net> wrote in message
news:%23$VL3Vmy FHA.2540@TK2MSF TNGP09.phx.gbl. ..[color=blue]
> 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)\SQLEXPR ESS;"
> strConn &= "Database = NorthWind; Integrated Security = SSPI;"
> conn = New SqlConnection(s trConn)
>
> da = New SqlDataAdapter( "Select Row_Number() Over(Order by
> CustomerID) as MyOrdinal, * From Customers", conn)
> da.Fill(ds, "Customers" )
>
> DataGridView1.D ataSource = ds.Tables("Cust omers")
>
>
> Ken
> ----------------------------------------
> "Wayne Wengert" <wayneSKIPSPAM@ wengert.org> wrote in message
> news:ul$$WImyFH A.2348@TK2MSFTN GP15.phx.gbl...[color=green]
>>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
>>[/color]
>
>[/color]

[Mr Newbie]

Re: Looking For A Better Way

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.


"Wayne Wengert" <wayneSKIPSPAM@ wengert.org> wrote in message
news:%23p4kpYmy FHA.2812@TK2MSF TNGP14.phx.gbl. ..[color=blue]
> 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" <here@now.com > wrote in message
> news:uQzP7RmyFH A.2812@TK2MSFTN GP14.phx.gbl...[color=green]
>> 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.
>>
>>
>>
>>
>>
>>
>> "Wayne Wengert" <wayneSKIPSPAM@ wengert.org> wrote in message
>> news:ul$$WImyFH A.2348@TK2MSFTN GP15.phx.gbl...[color=darkred]
>>>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
>>>[/color]
>>
>>[/color]
>
>[/color]

[AMercer]

RE: Looking For A Better Way

> I have a VB VSE 2005 project in which I need to rank about 35 columns of[color=blue]
> 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.[/color]

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]

Re: Looking For A Better Way

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

"Ken Tucker [MVP]" <vb2ae@bellsout h.net> wrote in message
news:%23$VL3Vmy FHA.2540@TK2MSF TNGP09.phx.gbl. ..[color=blue]
> 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)\SQLEXPR ESS;"
> strConn &= "Database = NorthWind; Integrated Security = SSPI;"
> conn = New SqlConnection(s trConn)
>
> da = New SqlDataAdapter( "Select Row_Number() Over(Order by
> CustomerID) as MyOrdinal, * From Customers", conn)
> da.Fill(ds, "Customers" )
>
> DataGridView1.D ataSource = ds.Tables("Cust omers")
>
>
> Ken
> ----------------------------------------
> "Wayne Wengert" <wayneSKIPSPAM@ wengert.org> wrote in message
> news:ul$$WImyFH A.2348@TK2MSFTN GP15.phx.gbl...[color=green]
>>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
>>[/color]
>
>[/color]