开发者

How to get one common value from Database using UNION

How to get one common value from Database using UNION

2 records in above image are from Db, in above table Constraint are (SID and LINE_ITEM_ID), SID and LINE_ITEM_ID both column are used to find a unique record.

My issues : I am looking for a query it should fetch the recored from DB depending on conditions if i search for PART_NUMBER = 'PAU43-IMB-P6开发者_Python百科' 1. it should fetch one record from DB if search for PART_NUMBER = 'PAU43-IMB-P6', no mater to which SID that item belong to if there is only one recored either under SID =1 or SID = 2. 2. it should fetch one record which is under SID = 2 only, from DB on search for PART_NUMBER = 'PAU43-IMB-P6', if there are 2 items one in SID=1 and other in SID=2.

i am looking for a query which will search for a given part_number depending on Both SID 1 and 2, and it should return value under SID =2 and it can return value under SID=1 only if the there are no records under SID=2 (query has to withstand a load of Million record search).

Thank you


Select * 
  from Table
 where SID||LINE_ITEM_ID = (
                            select Max(SID)||Max(LINE_ITEM_ID) 
                              from table 
                             where PART_NUMBER = 'PAU43-IMB-P6'
                           );


If I understand correctly, for each considered LINE_ITEM_ID you want to return only the one with the largest value for SID. This is a common requirement and, as with most things in SQL, can be written in many different ways; the best performing will depend on many factors, not least of which is the SQL product you are using.

Here's one possible approach:

SELECT DISTINCT * -- use a column list
  FROM YourTable AS T1
       INNER JOIN (
                   SELECT T2.LINE_ITEM_ID, 
                          MAX(T2.SID) AS max_SID 
                     FROM YourTable AS T2
                    GROUP
                       BY T2.LINE_ITEM_ID
                  ) AS DT1 (LINE_ITEM_ID, max_SID)
          ON T1.LINE_ITEM_ID = DT1.LINE_ITEM_ID 
             AND T1.SID = DT1.max_SID;

That said, I don't recall seeing one that relies on the UNION relational operator. You could easily rewrite the above using the INTERSECT relational operator but it would be more verbose.


Well in my case it worked something like this:

select LINE_ITEM_ID,SID,price_1,part_number from (
(select LINE_ITEM_ID,SID,price_1,part_number from Table where SID = 2)
 UNION 
(select LINE_ITEM_ID,SID,price_1,part_number from Table  SID = 1 and line_item_id NOT IN (select LINE_ITEM_ID,SID,price_1,part_number from Table  SID = 2)))

This query solved my issue..........

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜