[RESOLVED] How to union two sets then take only the top distinct row?

I query seperately two sets of data which returns identical columns.
The final column of both sets is a rank. My problem is that both sets (excluding the rank can contain the same data)

I then want to union the sets so I have a distinct list of data, however I want the final set ordered by rank.

This is causing me problems because the results dont get unioned properly as the identical records with different rankings remain, I only really want the top ranked result from the duplicates when put together into one set.

Any ideas how to do this, i.e. if it is in the first set dont include the record from the second set!

Code:

---

SELECT        DISTINCT c.gsp_course_id
                                ,        c.supplier_id                               
                                ,        c.course_code
                                ,  c.title
                                ,        duration
                                ,  999 as        RANKING
                                FROM [Live].[tbl_Courses] c                       
        INNER JOIN (
                                                                        SELECT course_code, location, datediff(dd,[start],[end])+1 as duration       
                                                                        FROM [Live].[VW_Events] WITH (NOLOCK)
                                                                        WHERE
                                                                                start > CURRENT_TIMESTAMP                                                                                                       
                                ) evfuture
                                ON c.course_code = evfuture.course_code                       
                        WHERE       
                        (
                                                (c.course_code LIKE ('%' + @search_text + '%'))
                                                        OR
                                                (c.title LIKE ('%' + @search_text + '%'))
                        )                               
UNION
       

                                SELECT        DISTINCT 0
                                ,        1                               
                                ,        co.Course_CourseCode
                                ,  co.Course_Title
                                ,        co.Course_Duration
                                ,        Key_TBL.Rank as RANKING
               
                FROM Corp_Rpt.Common.VW_COURSES co       
                INNER JOIN (
                                                                        SELECT Event_CourseCode
                                                                               
                                                                        FROM Corp_Rpt.Common.VW_EVENTS WITH (NOLOCK)
                                                                        WHERE
                                                                                Event_StartDate > CURRENT_TIMESTAMP
                                                                                                       
                                                        ) evfuture
                                        ON co.Course_CourseCode = evfuture.Event_CourseCode

                INNER JOIN Live.tbl_CourseOutlines col on co.Course_CourseCode = col.course_code and co.Course_Active  !='N'
                INNER JOIN CONTAINSTABLE (Live.tbl_CourseOutlines, *, @criteria
        )
        AS KEY_TBL
        ON col.gsp_outline_id = Key_TBL.[key]

---

I have tried inserting both into a temp table (without the rank, and doing a distinct but then it keeps the record from the second set.

If you use UNION then only distinct rows are included in the final dataset.
Also use Order By clause if you need to sort by specific column(s) - in your case it could be Order By 6 at the end of entire statement.

Yes but the column six is what is making all rows distinct, but I cant get rid of it as this is also the column I need to have the rows ordered by.

Not sure what you mean really. Regardless of wether or not records are distinct you can still sort on any column by providing column name or index in the Order By clause.
Have you even tried that yet?

Ok the end result is I want my result set sorted by order of ranking, however to do so I have to have ranking in my result set. If I have ranking in the result set then I cannot get a distinct list as records as the duplicate records all have a different ranking.

No time to analize it further, but dig into this

SELECT DISTINCT supplier_id, course_code, title, MAX(rank) AS Ranking
FROM YourUnifiedQuery
GROUP BY supplier_id, course_code, title
ORDER BY rank

Could you use something like this?

Code:

---

declare @temp table(data char(1), rank int)

insert into @temp(data,rank) values
('a',1),('a',2),('b',3),('b',4)

select data, MAX(rank)
from @temp
group by data

---

Yes in the end i inserted my first set of data into a temp table then selected from the temp table unioning it the a seelect statement for the second set which had a left join on the temp table with a math of null.

This should have excluded any in the second set which were already in the first set.