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?
 
PortletPaulfreelancerCommented:
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
 
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
 
PortletPaulfreelancerCommented:
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
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.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
chrismichalczuk, do you still need help with this question?
0
 
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
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.