Duplicate - Row Priority Union Issue

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Duplicate - Row Priority Union Issue

Author

Topic

visa123
Yak Posting Veteran

54 Posts

Posted - 2012-03-03 : 02:16:57

Hi

Duplication issue in the below Query (with the problem of "as ROW Priority").

1 10083 J1 Birmingham Alabama
1 200 J2 Birmingham Alabama
2 10083 J3 Birmingham Alabama

SELECT TOP 3 * FROM (SELECT * FROM ((SELECT TOP 1 1 as ROW, JobID,ISNULL(JobTitle,'') as JobTitle,ISNULL(City,'') as City,ISNULL(State,'') as State,ISNULL(ClassLevel3,'') as Division,ISNULL(PostedDate,'') as PostedDate,CASE WHEN ((Len(isnull(RTRIM(LTRIM(ShortState)),''))>0) AND CHARINDEX(',',ShortState)<=0) THEN ShortState ELSE 'Multiple'END as Location1,CASE WHEN ((Len(isnull(RTRIM(LTRIM(State)),''))>0) AND CHARINDEX(',',State)<=0) THEN State ELSE 'Multiple'END as Location2,CASE WHEN ((Len(isnull(RTRIM(LTRIM(City)),''))>0) AND CHARINDEX(',',City)<=0) THEN City ELSE 'Multiple' END as Location FROM DV_CareerSite_Jobdetail WITH (NOLOCK) WHERE extractedInLatestRun=1 and (ClassLevel3 IS NOT NULL AND ClassLevel3 <>'') and JobID in ('200'))Union(SELECT TOP 1 1 as ROW, JobID,ISNULL(JobTitle,'') as JobTitle,ISNULL(City,'') as City,ISNULL(State,'') as State,ISNULL(ClassLevel3,'') as Division,ISNULL(PostedDate,'') as PostedDate,CASE WHEN ((Len(isnull(RTRIM(LTRIM(ShortState)),''))>0) AND CHARINDEX(',',ShortState)<=0) THEN ShortState ELSE 'Multiple'END as Location1,CASE WHEN ((Len(isnull(RTRIM(LTRIM(State)),''))>0) AND CHARINDEX(',',State)<=0) THEN State ELSE 'Multiple'END as Location2,CASE WHEN ((Len(isnull(RTRIM(LTRIM(City)),''))>0) AND CHARINDEX(',',City)<=0) THEN City ELSE 'Multiple' END as Location FROM DV_CareerSite_Jobdetail WITH (NOLOCK) WHERE extractedInLatestRun=1 and (ClassLevel3 IS NOT NULL AND ClassLevel3 <>'') and JobID in ('10083')) ) a UNION SELECT * FROM (SELECT TOP 3 2 as ROW, JobID,ISNULL(JobTitle,'') as JobTitle,ISNULL(City,'') as City,ISNULL(State,'') as State,ISNULL(ClassLevel3,'') as Division,ISNULL(PostedDate,'') as PostedDate,CASE WHEN ((Len(isnull(RTRIM(LTRIM(ShortState)),''))>0) AND CHARINDEX(',',ShortState)<=0) THEN ShortState ELSE 'Multiple'END as Location1,CASE WHEN ((Len(isnull(RTRIM(LTRIM(State)),''))>0) AND CHARINDEX(',',State)<=0) THEN State ELSE 'Multiple'END as Location2,CASE WHEN ((Len(isnull(RTRIM(LTRIM(City)),''))>0) AND CHARINDEX(',',City)<=0) THEN City ELSE 'Multiple' END as Location FROM DV_CareerSite_Jobdetail WITH (NOLOCK) WHERE extractedInLatestRun=1 ) b ) C Order By Row Asc,PostedDate Desc

can anyone help for the above query?

Visa.G

visa123
Yak Posting Veteran

54 Posts

Posted - 2012-03-03 : 02:41:37

hi

I found the Solution using PARTITION BY.

Answer :

SELECT * FROM (

SELECT ROW_NUMBER() OVER (PARTITION BY Jobid,Location1,Location2,Location Order By Row Asc,PostedDate Desc) AS 'RowNumber',* FROM

(SELECT * FROM ((SELECT TOP 1 1 as ROW, JobID,ISNULL(JobTitle,'') as JobTitle,ISNULL(City,'') as City,ISNULL(State,'') as State,ISNULL(ClassLevel3,'') as Division,ISNULL(PostedDate,'') as PostedDate,CASE WHEN ((Len(isnull(RTRIM(LTRIM(ShortState)),''))>0) AND CHARINDEX(',',ShortState)<=0) THEN ShortState ELSE 'Multiple'END as Location1,CASE WHEN ((Len(isnull(RTRIM(LTRIM(State)),''))>0) AND CHARINDEX(',',State)<=0) THEN State ELSE 'Multiple'END as Location2,CASE WHEN ((Len(isnull(RTRIM(LTRIM(City)),''))>0) AND CHARINDEX(',',City)<=0) THEN City ELSE 'Multiple' END as Location FROM DV_CareerSite_Jobdetail WITH (NOLOCK) WHERE extractedInLatestRun=1 and (ClassLevel3 IS NOT NULL AND ClassLevel3 <>'') and JobID in ('200'))Union(SELECT TOP 1 1 as ROW, JobID,ISNULL(JobTitle,'') as JobTitle,ISNULL(City,'') as City,ISNULL(State,'') as State,ISNULL(ClassLevel3,'') as Division,ISNULL(PostedDate,'') as PostedDate,CASE WHEN ((Len(isnull(RTRIM(LTRIM(ShortState)),''))>0) AND CHARINDEX(',',ShortState)<=0) THEN ShortState ELSE 'Multiple'END as Location1,CASE WHEN ((Len(isnull(RTRIM(LTRIM(State)),''))>0) AND CHARINDEX(',',State)<=0) THEN State ELSE 'Multiple'END as Location2,CASE WHEN ((Len(isnull(RTRIM(LTRIM(City)),''))>0) AND CHARINDEX(',',City)<=0) THEN City ELSE 'Multiple' END as Location FROM DV_CareerSite_Jobdetail WITH (NOLOCK) WHERE extractedInLatestRun=1 and (ClassLevel3 IS NOT NULL AND ClassLevel3 <>'') and JobID in ('10083')) ) a UNION SELECT * FROM (SELECT TOP 3 2 as ROW, JobID,ISNULL(JobTitle,'') as JobTitle,ISNULL(City,'') as City,ISNULL(State,'') as State,ISNULL(ClassLevel3,'') as Division,ISNULL(PostedDate,'') as PostedDate,CASE WHEN ((Len(isnull(RTRIM(LTRIM(ShortState)),''))>0) AND CHARINDEX(',',ShortState)<=0) THEN ShortState ELSE 'Multiple'END as Location1,CASE WHEN ((Len(isnull(RTRIM(LTRIM(State)),''))>0) AND CHARINDEX(',',State)<=0) THEN State ELSE 'Multiple'END as Location2,CASE WHEN ((Len(isnull(RTRIM(LTRIM(City)),''))>0) AND CHARINDEX(',',City)<=0) THEN City ELSE 'Multiple' END as Location FROM DV_CareerSite_Jobdetail WITH (NOLOCK) WHERE extractedInLatestRun=1 ) b ) C)D WHERE RowNumber=1 ORDER BY ROW ASC,Posteddate DESC

Visa.G

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources