How to filter on ROW_NUMBER()

Question

I am trying to select distinct NAME from a dataset but also return other columns. I have it working to a degree but just cant figure out how to bring it together.

I suspect I need a WITH x( or something but am unsure

Here is the CODE and an image of the data it returns. From here I want to only show WHERE RN=1 shown circled in red in the image

Select
    row_number() over (partition by tagname order by adddate) as RN,
    tagname,
    RIGHT(v_AlarmsWithTagname.TagName,charindex('.',REVERSE(v_AlarmsWithTagname.TagName))-1) as SCADA_tag, 
    convert(varchar(12) , adddate , 101) as AddDate,
    left(tagname,CHARINDEX('.',tagname)-1) as 'Table',
    [CC_NOTE_LOG].dbo.SCADA_DB.groupid as 'Group',
    [CC_NOTE_LOG].dbo.SCADA_DB.dataset as 'Dataset', 
    [CC_NOTE_LOG].dbo.SCADA_DB.Description as 'Description'
FROM "Buckeye KB".dbo.v_AlarmsWithTagname
join 
    [CC_NOTE_LOG].dbo.SCADA_DB
on 
    RIGHT(v_AlarmsWithTagname.TagName,charindex('.',REVERSE(v_AlarmsWithTagname.TagName))-1)=[CC_NOTE_LOG].dbo.SCADA_DB.SCADA_SR_TAG
where
    [CC_NOTE_LOG].dbo.SCADA_DB.groupid<>'test' and
    [CC_NOTE_LOG].dbo.SCADA_DB.groupid not like 'Keep%' and
    [CC_NOTE_LOG].dbo.SCADA_DB.groupid not like 'delete%' and
    Tagname not like '%.ES_%' and
    Tagname not like '%.OPC_%'

Answer 1

You just need a common table expression (CTE). For this you use the following syntax: ;with CTE AS (query) SELECT whatever FROM CTE ...Please take a look at below code block.

;with CTE AS (
Select
    row_number() over (partition by tagname order by adddate) as RN,
    tagname,
    RIGHT(v_AlarmsWithTagname.TagName,charindex('.',REVERSE(v_AlarmsWithTagname.TagName))-1) as SCADA_tag, 
    convert(varchar(12) , adddate , 101) as AddDate,
    left(tagname,CHARINDEX('.',tagname)-1) as 'Table',
    [CC_NOTE_LOG].dbo.SCADA_DB.groupid as 'Group',
    [CC_NOTE_LOG].dbo.SCADA_DB.dataset as 'Dataset', 
    [CC_NOTE_LOG].dbo.SCADA_DB.Description as 'Description'
FROM "Buckeye KB".dbo.v_AlarmsWithTagname
join 
    [CC_NOTE_LOG].dbo.SCADA_DB
on 
    RIGHT(v_AlarmsWithTagname.TagName,charindex('.',REVERSE(v_AlarmsWithTagname.TagName))-1)=[CC_NOTE_LOG].dbo.SCADA_DB.SCADA_SR_TAG
where
    [CC_NOTE_LOG].dbo.SCADA_DB.groupid<>'test' and
    [CC_NOTE_LOG].dbo.SCADA_DB.groupid not like 'Keep%' and
    [CC_NOTE_LOG].dbo.SCADA_DB.groupid not like 'delete%' and
    Tagname not like '%.ES_%' and
    Tagname not like '%.OPC_%'
)
SELECT * FROM CTE WHERE [RN] = 1

It is also important to note that the CTE doesn't have to be directly followed with a SELECT. Please see the following link for CTE guidelines: https://msdn.microsoft.com/en-us/library/ms175972.aspx