Search code examples
sql-servert-sqlprogress-4glopenqueryprogress-db

SQL Distinct on First Column OpenQuery

I'm using an OpenQuery in SQL Server 2014 to retrieve data from Progress.

This is my query:

SELECT *
FROM OPENQUERY(PRG, 'SELECT "cde","dsc" FROM tblCodes')

And it retrieves data like this:

cde     dsc
===     =====
one     test
one     another
one     value
two     goes
two     here
two     also
three   example

However, I need to have results look like this:

cde     dsc
===     =====
one     test
two     goes
three   example

How do I do this in an OpenQuery?

Solution

  • In your open query your query should be like below and I recommend you to have the cde column as a numeric ID :

     WITH CTE AS (select cde,dsc,
    row_number() over(
                        partition by cde
                        order by cde
                    ) as rn

from tblCodes 
)
select cde,dsc from CTE 
where rn =1

    Check the execution here : sqlfiddle