How to get the latest entry in a field? asp.net, sql

I have a field called LongNotes and I need to get the latest entry in that field. Here's my current query:

 
<asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:qConnectionString %>" SelectCommand="SELECT ACCOUNT.ACCOUNTID, HISTORY.STARTDATE, HISTORY.USERNAME, HISTORY.CONTACTNAME, HISTORY.LONGNOTES FROM ACCOUNT INNER JOIN HISTORY ON ACCOUNT.ACCOUNTID = HISTORY.ACCOUNTID WHERE (ACCOUNT.ACCOUNTID = @Param1)">
             <SelectParameters>
                 <asp:QueryStringParameter Name="Param1" QueryStringField="ACCOUNTID" />
             </SelectParameters>
         </asp:SqlDataSource>

Open in new window


I have to get the data this way so I don't want to mess anything up. I just need to somehow get the last entry in the HISTORY.LONGNOTES field.

Any ideas? TIA!

mh
LVL 7
MHenryAsked:
Who is Participating?
 
Anthony PerkinsCommented:
Making some assumptions would lead me to something like this:
SELECT  a.ACCOUNTID,
		h.STARTDATE,
		h.USERNAME,
		h.CONTACTNAME,
		h.LONGNOTES
FROM    ACCOUNT a
		INNER JOIN HISTORY h ON a.ACCOUNTID = h.ACCOUNTID
		INNER JOIN (
			SELECT	ACCOUNTID,
				MAX(STARTDATE) LatestSTARTDATE
			FROM	History
			GROUP BY
				ACCOUNTID) h2 ON a.ACCOUNTID = h2.ACCOUNTID AND h.STARTDATE = h2.LatestSTARTDATE
WHERE   a.ACCOUNTID = @Param1

Open in new window

But you can also do this with a CTE as well.
0
 
Anthony PerkinsCommented:
I have a field called LongNotes and I need to get the latest entry in that field.
Define "latest".  If it is based on a date or IDENTITY column than post the actual schema of the table(s) involved and indicate the column.

Also, do you want the "latest" per ACCOUNTID?
0
 
MHenryAuthor Commented:
Anthony Perkins,

The only field that has more than one entry is the LongNotes. This is a Details view. I'm only pulling one value or row. I just need to make sure to grab the most recently created LongNote. Right now it seems to be grabbing the first one, not the last one.

mh
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Anthony PerkinsCommented:
I'm only pulling one value or row. I just need to make sure to grab the most recently created LongNote.
And you still have not defined that.  What do you mean by the "most recently created LongNote"? Do you mean based on the STARTDATE?  If so than see the query I posted earlier.
0
 
dejaanbuCommented:
try this:
if latest entry is based on STARTDATE,
select a.ACCOUNTID, h.STARTDATE, h.USERNAME, h.CONTACTNAME, h.LONGNOTES FROM ACCOUNT a
inner 
  join HISTORY  h
    on a.ACCOUNTID=h.ACCOUNTID
   and h.STARTDATE= ( select max(STARTDATE)
           from HISTORY
          where ACCOUNTID=@Param1 ) and a.ACCOUNTID=@Param1 

Open in new window


If not, and if you have history id column in history table. then change
select a.ACCOUNTID, h.STARTDATE, h.USERNAME, h.CONTACTNAME, h.LONGNOTES FROM ACCOUNT a
inner 
  join HISTORY  h
    on a.ACCOUNTID=h.ACCOUNTID
   and h.HISTORYID= ( select max(HISTORYID)
           from HISTORY
          where ACCOUNTID=@Param1 ) and a.ACCOUNTID=@Param1

Open in new window

0
 
MHenryAuthor Commented:
Anthony Perkins,
Sorry, I didn't understand your question. I just responded without checking to see if your code worked. And it did.

Thanks,

dejaanbu,
Sorry for wasting your time. Anthony Perkins nailed it so I can't split points. I'm sure your code works as well but since he was first ...

Best,
mh
0
 
dejaanbuCommented:
No issues @MHenry.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.