How do I find the last rows each machine_id in mysql database with vb.net?

Question

'    machine_id   time_schedule  product_quantity
'        1                            100
'        1                            200          
'        1                            100          <<  find this value(100)
'        2            05:00:00
'        2            10:00:00
'        2            15:00:00                     <<  find this value(15:00:00)
'        3                            100           
'        3                            300          <<  find this value(300)
'        4                            200           
'        4                            100           
'        4                            50           <<  find this value(50)

How do I find the last rows each machine_id in mysql database with vb.net?? Thanks

    Private Sub btnAddSchedule_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddSchedule.Click
    Try
        Dim ts As TimeSpan = TimeSpan.Parse(Me.dtpSchedule.Text

        ' PROBLEM IN HERE ??? 
        Dim rwFound As DataRow = MyDataset.Tables("totalizer_setting").Rows.Find( ??? )
        If IsNothing(rwFound) Then
        Dim rw As DataRow = MyDataset.Tables("totalizer_setting").NewRow
        rw("machine_id") = Me.txtMachineID.Text
        rw("time_schedule") = ts
        rw("product_quantity") = DBNull.Value
        MyDataset.Tables("totalizer_setting").Rows.Add(rw)

        Dim sql As String = String.Format("INSERT INTO {0}.totalizer_setting (machine_id, time_schedule, product_quantity) VALUES ({1});", varSchema, "@machine_id, @time_schedule, @product_quantity")
        Dim cmd As New MySqlCommand(sql, objCon)
        cmd.Parameters.AddWithValue("@machine_id", Me.txtMachineID.Text)
        cmd.Parameters.AddWithValue("@time_schedule", ts)
        cmd.Parameters.AddWithValue("@product_quantity", DBNull.Value)
        MyDataAdapter.InsertCommand = cmd
        MyDataAdapter.Update(MyDataset, "totalizer_setting")
        cmd.Dispose()

       End If
    Catch ex As MySqlException
        MessageBox.Show("Error occurred in btnAddSchedule_Click Function" & vbCrLf & ex.Number & " – " & ex.Message)
    End Try
End Sub

see accepted answer in http://stackoverflow.com/questions/1379565/mysql-first-and-last-record-of-a-grouped-record-aggregate-functions for a more efficient solution — o17t H1H' S'k, Jun 25 '12 at 12:49

score 2 · Accepted Answer · edited May 23 '17 at 10:34

2

Something like this should work

select last(time_schedule), last(product_quality) from yourTable group by machine_id

On a second thought, I'm not if mysql has last() function (sql server does). If mysql doesn't have it, you can either use first() with reverse order or see the answer from this post

edited May 23 '17 at 10:34

Community

1
1

answered Jun 10 '11 at 13:57

Bala R

107,317
23
199
210

How do I find the last rows each machine_id in mysql database with vb.net?

1 Answers1