CODEWITHSUNDEEP
sqlsql-serverdistinct-values
BUSH
BUSH

Reputation: 45

SELECT statement avoid duplicating in multiple columns in SQL Server

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.

Upvotes: 0

Views: 2332

Answers (3)

Dharmesh Porwal
Dharmesh Porwal

Reputation: 1406

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) ;

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93704

Try this.

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

Upvotes: 2

Imtiaz Ali
Imtiaz Ali

Reputation: 13

Add Distinct keyword

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

Upvotes: 0

Related Questions