extract 1st record from asql query only

How do I extract ONLY the first record for each AccountID with these fields from this query. There are multiple records for each accountid but I only want the 1st record based on the termination_date order

SELECT
 [Account]
      ,[Accountid]
      ,[Createdate]
      ,[Created By]
      ,[No Termination Date]
      ,[Sequence No#]
      ,[Serverhash]
      ,[Status]
      ,[Termination Date]
      ,[Type]
      ,[Version Tc]
        FROM [Licenses]
  order by [Termination Date] desc
Chris MichalczukConsultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SimonCommented:
You can do this with a CTE (common table expression/intermediate result set)
;with cte as 
(SELECT
       (partition by Accountid order by [Termination Date] desc) as rn
       ,[Account]
      ,[Accountid]
      ,[Createdate]
      ,[Created By]
      ,[No Termination Date]
      ,[Sequence No#]
      ,[Serverhash]
      ,[Status]
      ,[Termination Date]
      ,[Type]
      ,[Version Tc]
        FROM [Licenses])
select * from cte 
where rn=1
order by [Termination Date] desc

Open in new window

0
PortletPaulEE Topic AdvisorCommented:
tiny note to the above solution; no points please

you do not have to use a common table expression (CTE) the same can be done using a "derived table" instead.

select * 
from (SELECT
       (partition by Accountid order by [Termination Date] DESC) as rn
      ,[Account]
      ,[Accountid]
      ,[Createdate]
      ,[Created By]
      ,[No Termination Date]
      ,[Sequence No#]
      ,[Serverhash]
      ,[Status]
      ,[Termination Date]
      ,[Type]
      ,[Version Tc]
        FROM [Licenses]
    ) derived
where rn=1

Open in new window


Oh, and note DESCending order of dates would give you "the most recent". If you want "oldest" (= "first") then use ASCending order instead.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
chrismichalczuk, do you still need help with this question?
0
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

Chris MichalczukConsultantAuthor Commented:
Paul got this when tried your solution

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'by'.

I'm using SQL 2012
0
Chris MichalczukConsultantAuthor Commented:
Simon got this when tried your solution too

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'by'.
0
PortletPaulEE Topic AdvisorCommented:
humble apologies:

select *
from (SELECT
     ROW_NUMBER() OVER (partition by Accountid order by [Termination Date] DESC) as rn

the bold portion missing from line 3
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.