OLEDB SELECT TOP 1 / MAX() only returning 4 digits

Question

Guys I'm doing a query to DB using OLEDB with the following Command:

OleDbCommand maxCommand = new OleDbCommand("SELECT TOP 1 id AS maxId FROM `tableName` ORDER BY id DESC", AppConstants.OLEDBCONNECTION);

And then printing the result:

maxCommand.CommandType = CommandType.Text;
OleDbDataReader reader = maxCommand.ExecuteReader();
reader.Read();
Int64 maxId = Int64.Parse(reader["maxId"].ToString()) + 1;

The thing is I've tried using MAX() and TOP 1 but both of them are returning 9999, when I know there are more than 10000 id values.

Help please, if you need any additional information please leave a comment.

What result do you get when you run the query directly on the database? — Siyual, Oct 14 '15 at 20:39
For several reasons I cannot run the query directly on the database sorry @Siyual But when I use the same process to search by `id` with `id=10001` I get a record — Vítor Martins, Oct 14 '15 at 20:41
@GiorgiNakeuri If I query `SELECT * FROM tercli WHERE id=10001` I get a record so it exists. — Vítor Martins, Oct 14 '15 at 20:48

Jakub Lortz · Accepted Answer · 2015-10-14T22:14:33.003

4

The data type of your id column is not int. It's probably a varchar column containing numbers. When you sort it, it's sorted in lexical order, not numeric.

To get the results you expect you can cast it to int (it won't be able to use index seek, so it will not be very efficient)

select max(cast (id as int)) ...

If it's possible, you should change the column type to int.

edited Oct 14 '15 at 22:14

answered Oct 14 '15 at 21:57

Jakub Lortz

14,616
3
25
39

Yes you're right, the Data Type is `OleDbType.Char`, but how can I still get the highest value? i.e. the highest number? – Vítor Martins Oct 14 '15 at 22:03
@VítorMartins You can cast it to int: `select max(cast (id as int)) ...`, although it won't be very efficient. If it's possible, change the column type to int. – Jakub Lortz Oct 14 '15 at 22:09

OLEDB SELECT TOP 1 / MAX() only returning 4 digits

1 Answers1