Sql Expression Field (1 Viewer)

[gfultz]

I have a query in which I want to pull the latest entry from another query. No matter how I have sliced it, it returns the latest for all records regardless of the mrid. Here is the code...

(Select Top 1 Expr1 FROM NOTEMERGE WHERE NOTEMERGE.MRID = [MRID])

The NOTEMERGE query grabs all of the notes with a certain flag and then concatenates 3 fields (Date, author, and note) and is sorting in descending order by dt. I just want this field to display the concatenated field for the latest note for that particular MRID. MRID exists on both queries.

The result I get:
MRID Expr1
1 12/23/2009 - note
12 12/23/2009 - note

What I want
MRID Expr1
1 12/18/2009 - notes
12 12/23/2009 - note

I believe the breakdown occurs in the WHERE clause...

 

[pbaldy]

The TOP predicate is somewhat meaningless without an ORDER BY clause. You might also consider this, if appropriate to your situation:

http://www.baldyweb.com/LastValue.htm

 

[gfultz]

Nice! Your article was perfect. Thanks!

 

[pbaldy]

No problem, glad it helped.