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

[FishGuy]

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!

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.

[RhinoBull]

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.

[FishGuy]

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.

[RhinoBull]

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?

[FishGuy]

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.

[kaliman79912]

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

[wild_bill]

Could you use something like this?

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

[FishGuy]

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.