MS SQL

Selecting record based on max date

Please support our MS SQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Apr 2007

Posts: 45

Reputation: Eager_Beever is an unknown quantity at this point

Solved Threads: 0

Eager_Beever Eager_Beever is offline

Offline

Light Poster

Selecting record based on max date

Mar 10th, 2009

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

•

Join Date: Apr 2007

Posts: 45

Reputation: Eager_Beever is an unknown quantity at this point

Solved Threads: 0

Eager_Beever Eager_Beever is offline

Offline

Light Poster

Re: Selecting record based on max date

Mar 11th, 2009

I am using the following query:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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)
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:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'where'.
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

Last edited by peter_budo; Mar 18th, 2009 at 7:25 am. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.

•

Join Date: Aug 2008

Posts: 1,162

Reputation: dickersonka will become famous soon enough

Solved Threads: 138

dickersonka dickersonka is offline

Offline

Veteran Poster

Re: Selecting record based on max date

Mar 11th, 2009

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

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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))
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))

Custom Application & Software Development
www.houseshark.net

•

Join Date: Apr 2007

Posts: 45

Reputation: Eager_Beever is an unknown quantity at this point

Solved Threads: 0

Eager_Beever Eager_Beever is offline

Offline

Light Poster

Re: Selecting record based on max date

Mar 11th, 2009

@"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 :

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT top 1 * FROM Results RT1 
WHERE RT1.RDate = (SELECT max(RT2.RDate) FROM Results RT2) 
ORDER BY cast(RTime as smalldatetime) desc
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 :

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT TOP 1 * FROM Results ORDER BY RDate DESC, cast(RTime as smalldatetime) DESC
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

Last edited by peter_budo; Mar 18th, 2009 at 7:27 am. Reason: Please use [code][/code] instead of [quote][/quote]

•

Join Date: Aug 2008

Posts: 1,162

Reputation: dickersonka will become famous soon enough

Solved Threads: 138

dickersonka dickersonka is offline

Offline

Veteran Poster

Re: Selecting record based on max date

Mar 11th, 2009

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

Custom Application & Software Development
www.houseshark.net