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


Trans table
Thanks in Advance
ZURINETAsked:
Who is Participating?
 
ralmadaConnect With a Mentor Commented:
If the order is not important, you can just do

select REPLACE( FileName, RIGHT(FileName, 4), '' )  as  FileName, max(FactACTID) FactACTID
 FROM Stat_Fact_ACT 
where FileName is not null
and CLTID = 100
group by REPLACE( FileName, RIGHT(FileName, 4), '' )
order by FileName desc

Open in new window



If not, I would just do one subquery

select * from (
select REPLACE( FileName, RIGHT(FileName, 4), '' )  as  FileName, FactACTID, row_number() over (partition by REPLACE( FileName, RIGHT(FileName, 4), '' ) order by REPLACE( FileName, RIGHT(FileName, 4), '' ) desc) rn
 FROM Stat_Fact_ACT 
where FileName is not null
and CLTID = 100
) a 
where rn = 1

Open in new window

0
 
Barry CunneyCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.