How to retreive the newest record in a database based on datetimefield

Question

What is the best way to retreive the newest record in a database based on datetimefield

select afield from a table where max(datefield)

select top 1 afield from a table order by (datefield) desc

Are the other options to do this ?

Answer 1

@Sneeze anyways, I think you need a subquery, to get your desired field from a row having most recent datetime value. Something like:

Select <yourDesiredField> from <tableName> where <recordUpdateField> = (Select max(<recordUpdateField>) from <tableName> )

Answer 2

@Sneeze then use:

Select max(adatefield) from atable;

This will return the most recent datetime field value. I hope I got you right, you only want the datetime field, not the row?

Hth, cmiiw

Answer 3

@Sneeze in MS Sql Server "TOP" is used, "LIMIT" is used with MySql.

https://www.w3schools.com/sql/sql_top.asp

Answer 4

LOL @Sneeze good for you, got me confused there a bit how could it be.

Answer 5

another option: SELECT afield FROM table ORDER BY datefield DESC LIMIT 0,1

Answer 6

Select * from atable where atable.adatefield > 'yyyy-mm-dd';

Answer 7

@sneeze how bad 😞

Answer 8

thanks ;-)

Answer 9

true, thanks a lot

Answer 10

Looks very neat. Unfortunately my sqlexpress does not recognize LIMIT as keyword.

so the query exceutes with ""incorrect syntax near LIMIT"

Answer 11

@Ipang I do not know the exact datetime. I just want the most recent value of the datetime field.

Answer 12

@ luca Do you know whether LIMIT is limited to a special version of sql

Answer 13

@Ipang I do not actually want the datetimefield. I do not want the record. There is afield I want the value of that field from the row that has the most recent datatimefield.

Answer 14

Is there a difference between sql server and the c# enviroment ?

I use this code to query the database. It seems the answer I get in c# are different from the sql server. Where the sql server is correct but I need my results in the c# enviroment.

try
{
cnn.Open();
cmd = new SqlCommand(sql, cnn);
reader = cmd.ExecuteReader();
while (reader.Read())
{
MessageBox.Show(reader.GetValue(0) );
}
reader.Close();
cmd.Dispose();
cnn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Can not open connection ! ");
}

Answer 15

Oeps I found my mistake.

It was in the sql-statement

"Select afield from atable where secondfield = secondfield"

Not using the variable secondfield but just have plain text there. Still I do not understand why it did return a record. But when I use the variable it does not return any result anymore as it should.