Delete a row with the highest value of a specific column

Question

I want to delete the row with the highest value on specific column

(I know that there are answers for that but none of them work for me for some reason)

All the coding I do in visual studio c# with SQL

MyDatabase: hours

Hours_Left | Hours_Spent | Time_Written | Mode | Time_Start | Time_End | Index
==============================================================================
aa         | bb          | cc           | dd   | ee         | ff       | 3
gg         | hh          | ii           | jj   | kk         | ll       | 4
mm         | nn          | oo           | pp   | qq         | rr       | 5
ss         | tt          | uu           | vv   | ww         | xx       | 6

[Hours_Left]   VARCHAR (MAX) NULL,
[Hours_Spent]  VARCHAR (MAX) NULL,
[Time_Written] VARCHAR (MAX) NULL,
[Mode]         VARCHAR (MAX) NULL,
[Time_Start]   VARCHAR (MAX) NULL,
[Time_End]     VARCHAR (MAX) NULL,
[Index]        INT           IDENTITY (1, 1) NOT NULL

My Code:

if (con.State != ConnectionState.Open)
        {
            con.Close();
            con.Open();
        }
        SqlCommand cmd = con.CreateCommand();
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = @"DELETE FROM hours WHERE Index = select max(Index) from hours limit 1";
        cmd.ExecuteNonQuery();
        con.Close();
        Display_Data();

Don't work either when commandText is

@"DELETE TOP(1) FROM hours
  WHERE Index in 
  (SELECT TOP(1) Index FROM hours
  ORDER BY Index DESC);";

I want to delete the whole row of the highest index - i.e delete the row with the index 6 that it is the highest index

Error at:

cmd.ExecuteNonQuery();

The error is:

System.Data.SqlClient.SqlException: 'Incorrect syntax near 'Index'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

Answer 1

Index is a keyword, you need to use [Index].