We help IT Professionals succeed at work.

extract 1st record from asql query only

Chris Michalczuk
Chris Michalczuk asked
on
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
Comment
Watch Question

SimonPrincipal Analyst

Commented:
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

PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
chrismichalczuk, do you still need help with this question?
Chris MichalczukConsultant

Author

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
Chris MichalczukConsultant

Author

Commented:
Simon got this when tried your solution too

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'by'.
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
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