Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 168
  • Last Modified:

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
0
MHenry
Asked:
MHenry
  • 3
  • 2
  • 2
1 Solution
 
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
 
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
 
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now