Jump to content

Distinct not working


Hogwilde1

Recommended Posts

I am wondering why this is not working. I have a table of order line items. each order can have multiple line items with the same transaction ID just different item codes.

I am trying to find single transaction ID's with a specific item code. I also tried doing a count (transactionnumber)<2 but could not figure out how to add a where for the item number.

So an order for just this item

 

select distinct(transactionnumber) from Orders (I thought this would select any unique transaction number IE only one occurance)where Code='9987'

When I run the above and check the transaction numbers it is any transaction with that code not transactions with only that code?

I also tried this

 

select * from OrdersWhere (select COUNT(transactionnumber)from Device_Orders) <2 and ItemCode='9987'

 

This returns no records when I know there are single transaction numbers with that item code?

 

Any suggestions would be appreciated

Link to comment
Share on other sites

When I run the above and check the transaction numbers it is any transaction with that code not transactions with only that code?

Correct, distinct returns all values in the column, but it only returns each value once. It will not return duplicates, it only returns the list of distinct values in the column.You need to use count and having:
SELECT transactionnumber, COUNT(*) AS num FROM Orders WHERE Code='9987' GROUP BY transactionnumber HAVING COUNT(*) = 1
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...