Jump to content

Distinct not working


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 post
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 post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...