Get a max value from a column

[bfsog]

Hey. I am trying to return a value from a column if it is the highest value in all rows.
However, if there is 2 or more rows with the same max value, I use a for loop to extract that rows Name (Name is a column)

But.. iMaxBreaks is always 0. So it only returns rows that the column break_count's value is 0. But 0 is not the highest value

	Private Sub frmViewStatistics_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

		' this sub will handle the connection to the database

		' these variables will hold the returned values from the database queries
		Dim iMaxBreaks As Integer
		Dim i As Integer = 0 ' used to control the for loop
	    Me.OleDbConnection2.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Application.StartupPath & "\mydb.mdb"
		Me.OleDbConnection2.Open() ' open connection to my database
		Try
		    Me.OleDbSelectCommand1.CommandText = "SELECT MAX(break_count) FROM users" ' select the row where break_count is the highest
			Me.OleDbSelectCommand1.Connection = OleDbConnection2
			OleDbDataAdapter1.Fill(dst, "Users")
			DataGrid1.DataSource = dst.Tables("Users")
		    SQLreturn = dst.Tables("Users").Rows.Count ' get number of rows
			MessageBox.Show(SQLreturn)
			    If Not dst.Tables("Users").Rows(i).Item("Break_Count") Is DBNull.Value Then
				    iMaxBreaks &= dst.Tables("Users").Rows(i).Item("Break_Count")
				End If
			MessageBox.Show(iMaxBreaks)
		    Me.OleDbSelectCommand1.CommandText = "SELECT * FROM users WHERE break_count = " & iMaxBreaks & ""
			Me.OleDbSelectCommand1.Connection = OleDbConnection2
			dst.Clear()
			OleDbDataAdapter1.Fill(dst, "Users")
			SQLreturn = dst.Tables("Users").Rows.Count
			MessageBox.Show(SQLreturn)
			For i = 0 To SQLreturn - 1
			    If Not dst.Tables("Users").Rows(i).Item("Break_Count") Is DBNull.Value Then
				    lblMostBreaks.Text &= dst.Tables("Users").Rows(i).Item("Name")
				    lblMostBreaks.Text += ", "
				End If
			Next
			MessageBox.Show(SQLreturn - 1)
		Catch ex As Exception
			MessageBox.Show(ex.Message)
		Catch ex As Exception
			MessageBox.Show(ex.Message)
		End Try
		Me.OleDbConnection2.Close()

	End Sub

To recap iMaxBreaks is always set to 0, not the max value

Any ideas what I am doing wrong?

 

[kulrom]

Me.OleDbSelectCommand1.CommandText = "SELECT TOP 1 break_count FROM users ORDER BY ID DESC" 'where ID is the primary key field

Cheers

 

[jmcilhinney]

Kulrom's code will work. Your's would have in theory except that whenever you use an aggregate function on a column the original column name is not retained. This means that MAX(break_count) does not create a column called break_count. You need to provide an alias for the column, e.g. MAX(break_count) AS max_break_count, or acces the column using its numerical index.

Also, you don't need to count the number of rows in the table because Fill is a function that returns the number of rows it retrieves.

Finally, I haven't tested this but I believe you may be able to use a single command to find the maximum and return all rows with that value. Try this and see what happens:

SELECT * FROM users WHERE break_count = MAX(break_count)