Link to home
Start Free TrialLog in
Avatar of ZURINET
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


 
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
      

Open in new window


User generated image
Thanks in Advance
Avatar of Barry Cunney
Barry Cunney
Flag of Ireland image

SELECT
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
ASKER CERTIFIED SOLUTION
Avatar of ralmada
ralmada
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial