Selecting record based on max date

[Eager_Beever]

I am working on an ASP.Net website with SQL Server database and C# 2005 as the programming language.

In my database I have two fields namely RDate and RTime. RDate stores the date of the record and RTime stores a time like 09:00 AM, 09:15 AM, 09:30 AM etc. The RTime field is in char data type. I also have a SrNo field of int data type and of identity property. Earlier I used to use the SrNo field to select the latest record. But unfortunately, the user added records in no particular order and my logic failed (this was an oversight from my part). Now I am not sure how to select the record (only 1 record) with the latest RDate and RTime value. (I have taken care to ensure that for a combination of RDate + RTime only one record can be added).

I think I have to use two select statements. One as max(RDate) to get the latest RDate and then use that value with another select statement to get the latest RTime. But I am not sure how to implement it.

Lalit Kumar Barik
India

[Eager_Beever]

I am using the following query:

select * from Results RT1 
where RT1.RDate = (select max(RT2.RDate) from Results RT2)
and RT1.RTime = (select max(RT3.RTime) from Results RT3)
where RT3.RDate = (select max(RT4.RDate) from Results RT4)

and I am getting the following error:

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'where'.

Where am I going wrong??

Please help me.

Lalit Kumar Barik

[dickersonka]

i'm not quite sure what you are trying to do here, but there are 2 things

first you are referencing R3 outside of its scope, second you have a where instead of an and, here is my shot at what i think you are trying to do

select * from Results RT1 
where RT1.RDate = (select max(RT2.RDate) from Results RT2)
and RT1.RTime = 
 (select max(RT3.RTime) from Results RT3
   where RT3.RDate = (select max(RT4.RDate) from Results RT4))

[Eager_Beever]

@"dickersonka". Thank you for pointing out the mistake in placing the crucial closing parenthesis in the proper place. Now the query is giving the proper result.

I wrote anothe query as :

select top 1 * from Results RT1 
where RT1.RDate = (select max(RT2.RDate) from Results RT2) 
order by cast(RTime as smalldatetime) desc

and still another as :

SELECT TOP 1 * FROM Results ORDER BY RDate DESC, cast(RTime as smalldatetime) DESC

ALL 3 queries above are giving the same result. Which one is better in terms of accuracy and performance??

Thank you.

Lalit Kumar Barik

[dickersonka]

Is the query taking that long to execute anyway?

I would suggest the bottom one, the less tables that are involved generally the more speed achieved, if performance is really that big of an issue with this query, then the cast will be slower but I don't think its that big of a deal here