sql distinct with multiple fields requried

Question

I'm using Advantage Database Server by Sybase. I need to elimiate duplicate addbatch's from my report, but having trouble pulling up just distinct records. Any idea what I am missing?

here is what I am using

SELECT  DISTINCT
    SI.[addbatch] as [Batch#],
    SI.[current account #] as [Account],
    SI.[status date] as [Status Date],
    SI.[SKU] as [SKU],
    AC.[email address] as [Email]
FROM  salesinventory SI, accounts AC
WHERE  AC.[account #]=SI.[current account #] and [Status Date] > '6/1/2015'

I still get duplicate addbatch's though. I'm not sure where I am going wrong! Thanks in advance! Wasn't even sure how to google this question!

Answer 1

-- The following code is generic to de-duplicate records, modify to suit your need.

select  x.[Well_Name] as nameX
      , x.[TestDate]  as dateX
from (
SELECT count(*) as dup
      ,[Well_Name]

      ,[TestDate]enter code here
  FROM [dbo].[WellTests]
  group by  [TestDate] ,[Well_Name] ) x
  where dup > 1

Answer 2

If you want to have unique batch numbers in your result, you have to GROUP BY the batch field only.

Something like this should work:

SELECT
    SI.[addbatch] as [Batch#],
    MIN(SI.[current account #]) as [Account],
    MIN(SI.[status date]) as [Status Date],
    MIN(SI.[SKU]) as [SKU],
    MIN(AC.[email address]) as [Email]
FROM  salesinventory SI, accounts AC
WHERE  AC.[account #]=SI.[current account #] and [Status Date] > '6/1/2015'
GROUP BY
  SI.[addbatch]

You didn't say how you want to aggregate the other columns, just replace MIN with something that makes more sense for you, like SUM or COUNT, etc.

There is a topic about grouping in the documentation.

PS: SELECT DISTINCT is (basically) just a shorter way to GROUP BY on all columns without any aggregation.

Answer 3

The problem is that you need to check uniqueness of a single column and that's not actually what your code is performing. Try this

SELECT  *
FROM    (SELECT SI.[addbatch] as [Batch#],
         SI.[current account #] as [Account],
         SI.[status date] as [Status Date],
         ETC,
             ROW_NUMBER() OVER (PARTITION BY [Batch#]) AS RowNumber
     FROM   salesinventory SI, accounts AC
     WHERE  AC.[account #]=SI.[current account #] and [Status Date] > '6/1/2015') as rec
WHERE rec.RowNumber = 1