get the latest record

i have a selection like this.

 FullName, DateCreated, NumVal

How do I return the FullName where numval = 1000 and also get the one with the latest DateCreated?
vbnetcoderAsked:
Who is Participating?
 
Rgonzo1971Commented:
Hi,

pls try
SELECT TOP 1 FullName, DateCreated, NumVal from tbl WHERE NumVal = 1000 ORDER BY DateCreated DESC

Open in new window

Regards
0
 
Éric MoreauSenior .Net ConsultantCommented:
or if you have multiple fullname for numval=1000:

select FullName, MAX(DateCreated) as DateCreated, NumVal
from YourTableName
where NumVal = 1000
Group By FullName, NumVal

Open in new window

0
 
PortletPaulfreelancerCommented:
If it is important to get the complete row ("record") then using row_number() over() achieves this. I frequently use this approach if folks ask for "the latest" or similar:
SELECT
      *
FROM (
      SELECT
            *
          , ROW_NUMBER() OVER (ORDER BY DateCreated DESC) AS rn
      FROM yourtable) d
      WHERE numval = 1000
WHERE rn = 1

Open in new window

1
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Mark WillsTopic AdvisorCommented:
agree with paul, row_number() works well...

I prefer the CTE - Common Table Expression - but much of a muchness

;with latest as
( select row_number() over (partition by numval order by datecreated desc) as rn,
       FullName, DateCreated, NumVal
  from yourtable
  where numval = 1000
) select FullName, DateCreated, NumVal
  from latest
  where rn = 1

Open in new window

0
 
awking00Commented:
>>How do I return the FullName where numval = 1000 and also get the one with the latest DateCreated?<<
 
Just to be clear. if you have data like this -
FULLNAME                 NUMVAL DATECREAT
-------------------- ---------- ---------
John Doe                    900 19-MAR-18
Mary Smith                 1000 09-MAR-18
Bill Jones                  800 28-MAR-18
Mary Smith                 1000 27-FEB-18

Would you only want to retrieve Mary Smith, 09-MAR-18 as the one with a numval of 1000 and the latest date with that numval, or would you also want to retrieve Bill Jones because it has the absolute latest date?
0
 
vbnetcoderAuthor Commented:
ty
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.