Member Avatar for emaduddeen

Hi Everyone,

Can you tell me how to fix my query so I can determine if an amount entered into a textbox is < the largest number in a database table?

Here is some sample data:

CategoryNumber
    ----------------
    10
    20
    30
    40
    50

If the user enters 35 I'm looking for the query to indicate that this number 35 is < the largest number which happens to be 50.

Here is the query I tried to use:

strSqlStatement =
                "Select 1 " & _
                  "From Categories " & _
                 "Where @CategoryNumber < Max(CategoryNumber) "

I get this error when running it so I know my query is wrong:

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Can you show me how to do this query so it will work?

Thanks.

Truly,
Emad

  • 2 Contributors
  • 2 Replies
  • 112 Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by emaduddeen

Dani AI

Generated

A few practical options and pitfalls based on the thread: ’s goal is to test whether the value entered in the textbox is less-than-or-equal-to the current highest stored CategoryNumber. pointed toward reading the table’s top value and comparing in code; that works. An alternative is to ask the database a yes/no question so the call returns a single small result and the engine can short-circuit quickly.

A safe, efficient server-side check uses an existence test (no aggregate in a WHERE). It returns a scalar you can read with ExecuteScalar and avoids scanning the whole table when an index is present.

-- returns 1 when enteredValue <= current maximum, 0 otherwise
SELECT CASE WHEN EXISTS(SELECT 1 FROM Categories WHERE CategoryNumber >= @EnteredValue) THEN 1 ELSE 0 END;

Example VB.NET pattern (parameterized, with input validation):

Dim v As Integer
If Not Integer.TryParse(TextBox1.Text, v) Then
    ' handle invalid input
End If

Using cn As New SqlConnection(connectionString)
    Using cmd As New SqlCommand("SELECT CASE WHEN EXISTS(SELECT 1 FROM Categories WHERE CategoryNumber >= @v) THEN 1 ELSE 0 END", cn)
        cmd.Parameters.Add("@v", SqlDbType.Int).Value = v
        cn.Open()
        Dim flag As Integer = Convert.ToInt32(cmd.ExecuteScalar())
        Dim isLessOrEqual As Boolean = (flag = 1)
    End Using
End Using

Notes and troubleshooting:

  • Always parameterize to avoid injection and set the correct SqlDbType (use Decimal/Precision for money/fractions).
  • If Categories is empty, the query returns 0; handle that case explicitly.
  • Add an index on CategoryNumber for fast exists checks.
  • If you must prevent race conditions (value can change between check and subsequent insert/update), perform the check-and-change inside a single transaction or stored procedure.

The original SQL failed because aggregates can’t be placed directly into a WHERE expression; the approaches above avoid that issue and give a simple, production-safe pattern.

Recommended Answers

All 2 Replies

Member Avatar for lolafuertes

If you need to retrieve the largest value from the table, doing a

SELECT MAX(CategoryNumber) FROM Categories

will return the expected value, that can be read using a data reader into an (i.e.) integer

The the result from the query can then be compared with the value entered on the text box converted to (i.e.) integer.

Hope this helps

Member Avatar for emaduddeen

Hi,

Thanks for the query.

Truly,
Emad

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.