ZURINET
asked on
Select Distinct from sub-query
Hi all
I have a table below
Given that I have ran the following query to truncate the values you are seeing..
I need to be able to isolate duplicate keys.. and return only the FactActID of one of the uniqe FileName.
In this case of File name.. (...T762163
I need a query that will only return the first FactID DBF9D....
And so on.. for the rest of the dupplicate records
Thanks in Advance
I have a table below
Given that I have ran the following query to truncate the values you are seeing..
I need to be able to isolate duplicate keys.. and return only the FactActID of one of the uniqe FileName.
In this case of File name.. (...T762163
I need a query that will only return the first FactID DBF9D....
And so on.. for the rest of the dupplicate records
select REPLACE( FileName, RIGHT(FileName, 4), '' ) as FileName, FactACTID
FROM Stat_Fact_ACT
where FileName is not null
and CLTID = 100
order by FileName desc
Thanks in Advance
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
FileName
,FactACTID
FROM
(
SELECT
FileName
,FactACTID
,ROW_NUMBER() OVER(PARTITION BY FileName) AS Row
FROM
(
select REPLACE( FileName, RIGHT(FileName, 4), '' ) as FileName
, FactACTID
FROM Stat_Fact_ACT
where FileName is not null
and CLTID = 100
) Fil
)Fil2
WHERE Row = 1
order by FileName desc