Select Value question in SSMS

15 pts.
Tags:
SQL Server Management
SQL Server Management Studio
SSMS
I am trying to select "log_comment" field with the max("stamp_date") field from "action_log" table where "field_index" field ='1007' I am getting more than just the row with the maximum stamp_date. Can someone please tell me what I'm doing wrong? Field_Index    Stamp_Date   Log_Comment 1007             2011-05-12    posting not complete 1007             2011-06-08    Pulled temporarily 1007             2011-04-15    PO needs to be received in Meditech please 1023             2011-06-13 10526           2011-04-06    General Matching    

Software/Hardware used:
SQL Server Management Studio

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    What's the query that you are running?
    66,010 pointsBadges:
    report
  • Sadams1
    select max(stamp_date) AS Stamp_Date, log_index, log_comment, stamp_uid from action_log where log_index = '1007' and doc_id = '49107052FD224F68BF64DA79E36E583E' group by stamp_date, log_index, log_comment, stamp_uid If I only select the stamp_date sql returns just the max stamp_date. but if I add in the other select fields I get all rows with log_index = '1007' for the doc_id
    15 pointsBadges:
    report
  • Kccrosser
    I am getting errors trying to put this in the answer box above... The problem is that the Group By is applied prior to the aggregate ("Max") function, and the Max function is thereby run on the result of the Group By, and you are including the "Stamp_Date" column in your Group By clause. Essentially, every unique occurrence of the Group By fields is being constructed in the result set, and then the Max is applied to that result. What you need to do is first obtain the max(stamp_date) value for the Field_Index values, then get the record data that match that combination. This requires a little more complex query:
    select
    	al1.Stamp_Date, al1.log_index, al1.log_comment, al1.stamp_uid
    from action_log al1
    where
    	al1.log_index = '1007' 
    	and al1.doc_id = '49107052FD224F68BF64DA79E36E583E'
    	and not exists (
    		select 1 from action_log al2
    		where al2.log_index = al1.log_index
    			and al2.doc_id = al1.doc_id
    			and al2.Stamp_Date > al1.Stamp_Date
    		)
    
    This will retrieve the latest entry for each combination of log_index and doc_id, by finding the one with the highest Stamp_Date.
    3,830 pointsBadges:
    report
  • Sadams1
    [...] 6. Kccrosser provided the approved answer to a select value question in SSMS. [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following