SELECT statement avoid duplicating in multiple columns in SQL Server

Question

I have a table called ItemTransaction with columns itemId, Qty, BatchNo columns. I want to select the items which all are having qty >=0 and <= 100, and itemid and batchno unique.

The problem now is a single item can be repeated in the different batchno with different qty.

select 
    ItemID, Quantity, BatchNo 
from  
    ItemTransaction 
where 
    Quantity >= 0 and Quantity <= 100 
group by 
    ItemID, Quantity, BatchNo

When I'm running the above query its giving duplicate values. Don't know how to fetch distinct values from the above mentioned condition.

Sample data

|ItemID | Quantity | BatchNo |
+----------------------------+
|1095   | 3        |   1     |
|1095   | 0        |   1     |
|1098   | 10       |   2     |
|1099   | 0        |   3     |
|1099   | 20       |   3     |
|1099   | 80       |   3     |

Expected output :

|ItemID | Quantity | BatchNo |
+----------------------------+
|1095   | 3        |   1     |
|1098   | 10       |   2     |
|1099   | 80       |   3     |

here quantity may be any which is greater in the batch or lesser in the batch.

I didn't quite understand the grouping. Can there be different quantities of the same item with the same batch number? — jhenninger
– jhenninger, Commented Nov 22, 2014 at 7:05
Add the expected output to avoid confusions make sure your sample data covers all your scenarious — Pரதீப்
– Pரதீப், Commented Nov 22, 2014 at 7:41
Your input data is already distinct. Perhaps you want distinct ItemID-BatchNo ? — toddlermenot
– toddlermenot, Commented Nov 22, 2014 at 7:54

Pரதீப் · Accepted Answer · 2014-11-22 07:50:54Z

2

Try this.

SELECT ItemID,
       Max(Quantity)Quantity,
       BatchNo
FROM   ItemTransaction 
WHERE  Quantity >= 0
       AND Quantity <= 100
GROUP  BY ItemID,
          BatchNo

answered Nov 22, 2014 at 7:50

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

marc_s · Accepted Answer · 2014-11-22 07:42:13Z

0

Add Distinct keyword

select Distinct ItemID, Quantity, BatchNo 
from ItemTransaction 
where Quantity >= 0 and Quantity <= 100 
group by ItemID,Quantity,BatchNo

edited Nov 22, 2014 at 7:42

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Nov 22, 2014 at 7:04

Imtiaz Ali

131 silver badge6 bronze badges

Comments

Dharmesh Porwal · Accepted Answer · 2014-11-22 08:41:48Z

0

add having clause after group by and distinct is correct too

 select ItemID,Quantity,BatchNo from ItemTransaction 
 where Quantity >= 0 and Quantity <= 100 
 group by ItemID,Quantity,BatchNo
 having count(ItemID)=1 ;

As when i posted my above answer your question was not complete; as of now it with expected output so the below query will give the correct answer .

  select ItemID,Quantity,BatchNo from Itm 
  where Quantity >= 0 and Quantity <= 100 and 
  (ItemID,Quantity) in 
  (select ItemID,max(Quantity) from itm group by ItemID) ;

edited Nov 22, 2014 at 8:41

answered Nov 22, 2014 at 7:31

Dharmesh Porwal

1,4062 gold badges12 silver badges22 bronze badges

2 Comments

BUSH Over a year ago

it will give you only the items which is having count 1. i don't think so it will help me, i want all the items but distinct values.

Dharmesh Porwal Over a year ago

By seeing your complete question and expected output you posted on edit ,so now your expected output query i posted it above

Collectives™ on Stack Overflow

SELECT statement avoid duplicating in multiple columns in SQL Server

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related