asked on

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

Simon

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

PortletPaul

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.