SQL Where Query to Return Distinct Values

Question

I have an app that has the built in initial Select option and only allows me to enter from the Where section. I have rows with duplicate values. I'm trying to get the list of just one record for each distinct value but am unsure how to get the statement to work. I've found one that almost does the trick but it doesn't give me any rows that had a dup. I assume due to the = so just need a way to get one for each that matches my where criteria. Examples below.

Initial Data Set

Date      | Name   | ANI         | CallIndex   | Duration
---------------------------------------------------------    
2/2/2015  | John   | 5555051000  | 00000.0001  | 60  
2/2/2015  | John   |             | 00000.0001  | 70 
3/1/2015  | Jim    | 5555051001  | 00000.0012  | 80  
3/4/2015  | Susan  |             | 00000.0022  | 90 
3/4/2015  | Susan  | 5555051002  | 00000.0022  | 30  
4/10/2015 | April  | 5555051003  | 00000.0030  | 35 
4/11/2015 | Leon   | 5555051004  | 00000.0035  | 10 
4/15/2015 | Jane   | 5555051005  | 00000.0050  | 20 
4/15/2015 | Jane   | 5555051005  | 00000.0050  | 60 
4/15/2015 | Kevin  | 5555051006  | 00000.0061  | 35

What I Want the Query to Return

Date      | Name   | ANI         | CallIndex   | Duration
---------------------------------------------------------    
2/2/2015  | John   | 5555051000  | 00000.0001  | 60
3/1/2015  | Jim    | 5555051001  | 00000.0012  | 80
3/4/2015  | Susan  | 5555051002  | 00000.0022  | 30
4/10/2015 | April  | 5555051003  | 00000.0030  | 35
4/11/2015 | Leon   | 5555051004  | 00000.0035  | 10
4/15/2015 | Jane   | 5555051005  | 00000.0050  | 20
4/15/2015 | Kevin  | 5555051006  | 00000.0061  | 35

Here is what I was able to get but when i run it I don't get the rows that did have dups callindex values. duration doesn't mattern and they never match up so if it helps to query using that as a filter that would be fine. I've added mock data to assist.

use Database
SELECT * FROM table
WHERE Date between '4/15/15 00:00' and '4/15/15 23:59'
and callindex in
                (SELECT callindex 
                 FROM table 
                 GROUP BY callinex 
                 HAVING COUNT(callindex) = 1)

Any help would be greatly appreciated.

Ok with the assistance of everyone here i was able to get the query to work perfectly within SQL. That said apparently the app I'm trying this on has a built in character limit and the below query is too long. This is the query i have to use as far as the restrictions and i have to be able to search both ID's at the same time because some get stamped with one or the other rarely both. I'm hoping someone might be able to help me shorten it?

use Database
select * from tblCall
WHERE 
    flddate between '4/15/15 00:00' and '4/15/15 23:59'
    and fldAgentLoginID='1234'
    and fldcalldir='incoming'
    and fldcalltype='external'
    and EXISTS (SELECT * FROM (SELECT MAX(fldCallName) AS fldCallName, fldCallID FROM tblCall GROUP BY fldCallID) derv WHERE tblCall.fldCallName = derv.fldCallName AND tblCall.fldCallID = derv.fldCallID)
or
    flddate between '4/15/15 00:00' and '4/15/15 23:59'
    and '4/15/15 23:59'
    and fldPhoneLoginID='56789'
    and fldcalldir='incoming'
    and fldcalltype='external'
    and EXISTS (SELECT * FROM (SELECT MAX(fldCallName) AS fldCallName, fldCallID FROM tblCall GROUP BY fldCallID) derv WHERE tblCall.fldCallName = derv.fldCallName AND tblCall.fldCallID = derv.fldCallID)

Answer 1

If the constraint is that we can only add to the WHERE clause, I don't think it's possible, due to there being 2 absolutely identical rows:

 4/15/2015 | Jane   | 5555051005  | 00000.0050    
 4/15/2015 | Jane   | 5555051005  | 00000.0050 

Is it possible that you can add HAVING or GROUP BY to the WHERE? or possibly UNION the SELECT to another SELECT statement? That may open up some additional possibilities.

Answer 2

From your sample, it seems like you could just exclude rows in which there was no value in the ANI column. If that is the case you could simply do:

use Database
SELECT * FROM table
WHERE Date between '4/15/15 00:00' and '4/15/15 23:59'
and ANI is not null

If this doesn't work for you, let me know and I can see what else I can do.

Edit: You've made it sound like the CallIndex combined with the Duration is a unique value. That seems somewhat doubtful to me, but if that is the case you could do something like this:

use Database
SELECT * FROM table
WHERE Date between '4/15/15 00:00' and '4/15/15 23:59'
and cast(callindex as varchar(80))+'-'+cast(min(duration) as varchar(80)) in
                (SELECT cast(callindex as varchar(80))+'-'+cast(min(duration) as varchar(80)) 
                 FROM table 
                 GROUP BY callindex)

Answer 3

There are two keywords you can use to get non-duplicated data, either DISTINCT or GROUP BY. In this case, I would use a GROUP BY, but you should read up on both.

This query groups all of the records by CallIndex and takes the MAX value for each of the other columns and should give you the results you want:

SELECT MAX(Date) AS Date, MAX(Name) AS Name, MAX(ANI) AS ANI, CallIndex
FROM table
GROUP BY CallIndex

EDIT

Since you can't use GROUP BY directly but you can have any SQL in the WHERE clause you can do:

SELECT *
FROM table
WHERE EXISTS
(
    SELECT *
    FROM 
    (
       SELECT MAX(Date) AS Date, MAX(Name) AS Name, MAX(ANI) AS ANI, CallIndex
       FROM table
       GROUP BY CallIndex   
    ) derv
    WHERE table.Date = derv.Date 
         AND table.Name = derv.Name
         AND table.ANI = derv.ANI
         AND table.CallIndex = derv.CallIndex
)

This selects all rows from the table where there exists a matching row from the GROUP BY.

It won't be perfect, if any two rows match exactly, you'll still have duplicates, but that's the best you'll get with your restriction.

Answer 4

Maybe with an union:

SELECT *
FROM table
GROUP BY Date, Name, ANI, CallIndex
HAVING ( COUNT(*) > 1 )

UNION

SELECT *
FROM table
WHERE Name not in (SELECT name from table
GROUP BY Date, Name, ANI, CallIndex
HAVING ( COUNT(*) > 1 ))

Answer 5

In your data, why not just do this?

SELECT *
FROM table
WHERE Date >= '2015-04-15' and Date < '2015-04-16'
      ani is not null;

If the blank values are only a coincidence, then you have a problem just using a where clause. If the results are full duplicates (no column has a different value), then you probably cannot do what you want with just a where clause -- unless you are using SQLite, Oracle, or Postgres.