Ad:

MS SQL Discussion Thread
Unsolved
Views: 5777

Mar 10th, 2009

Selecting record based on max date

Expand Post »

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

Similar Threads

opening files in classes in C++

Eager_Beever

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

45 posts
since Apr 2007

Permalink

Mar 11th, 2009

Re: Selecting record based on max date

I am using the following query:

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:

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.

Eager_Beever

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

45 posts
since Apr 2007

Permalink

Mar 11th, 2009

Re: Selecting record based on max date

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

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))

dickersonka

Reputation Points: 133

Solved Threads: 141

Veteran Poster

Offline

1,162 posts
since Aug 2008

Permalink

Mar 11th, 2009

Re: Selecting record based on max date

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

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 :

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]

Eager_Beever

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

45 posts
since Apr 2007

Permalink

Mar 11th, 2009

Re: Selecting record based on max date

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

dickersonka

Reputation Points: 133

Solved Threads: 141

Veteran Poster

Offline

1,162 posts
since Aug 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: Copy data from 2 tables in 2 databases

Next Thread in MS SQL Forum Timeline: AdventureWorks mayhem

DaniWeb Message

Cancel Changes

User Name
Password