Union and get Top1 for every record..?

In the below query I am trying to filter the records which has Type value of 'ABC' and 'XYZ'.

Each ca_id can have up to 3 Types.(ABC,XYZ,and ' ')

And also I have to get one empty type ,for which I am using union.

the problem is when i use union and pick top 1 ..I get 3 types for only for one ca_id at any time.and the remaining cases have 2 Types.(ABC and XYZ)

Can some one suggest ideas how to get an empty type for every ca_id record.

Thanks

select lo_id,ca_id,Type,Status,Category

from (

select lo_id,ca_id,Type,Status,Category

from (


select  distinct  ln.lo_id,cast.ca_id, Type,Status, 
 'Category'= case when Type='ABC' then 'ABC'
when Type='XYZ' then 'XYZ' else ' ' End

 FROM  ln                           
INNER JOIN cast ON cast.ca_id = ln.ca_id
INNER JOIN  Type ON Type.TypeId = cast .TypeId 

 ) as Q1
where  Category in ('ABC','XYZ')

union

select  top 1  lo_id,ca_id,Type,Status,(select Category where Category in (' ',null)) 
from (

select  distinct  ln.lo_id,cast.ca_id, Type,Status, 
 'Category'= case when Type='ABC' then 'ABC'
when Type='XYZ' then 'XYZ' else ' ' End

 FROM  ln   
INNER JOIN cast ON cast.ca_id = ln.ca_id
INNER JOIN  Type ON Type.TypeId = cast .TypeId
)  as Q2
) as Q3

Answers


You can do this without a union, using the row_number() function. The following gets all records with XYZ and ABC, and only the first for other categories:

select lo_id, ca_id, type, status, category
from (select distinct  ln.lo_id, cast.ca_id, Type, Status, 
             (case when Type='ABC' then 'ABC'
                   when Type='XYZ' then 'XYZ'
                   else ''
              End) as category,
             ROW_NUMBER() over (partition by ca_id, type) as seqnum
      FROM  ln INNER JOIN
            cast
            ON cast.ca_id = ln.ca_id INNER JOIN
            Type
            ON Type.TypeId = cast.TypeId 
     ) t
 where TYPE in ('ABC', 'XYZ') or seqnum = 1

By the way, CAST and TYPE are not good names for columns and aliases, since they have other meanings in SQL.


Need Your Help

How to access cordova file:/// image with BitmapFactory

java ios image cordova

I'm using a cordova google maps plugin and developed some sort of image cache for displaying markers.

Viewport keeps resizing on scroll

android jquery html css viewport

I'm new to the responsive designs and I've run into an odd problem with scrolling. It seems that every time I scroll, the viewport seems to keep re-sizing. I've tried using the <meta name="viewp...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.