We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Select distinct not working

Medium Priority
283 Views
Last Modified: 2012-05-11
Hello,

I don't understand why my query returns 2 records when written with SELECT DISTINCT:

SELECT distinct
CustomerNo,
StartDate,
StopDate,
SupplierID
                                                                        
FROM tmtB

WHERE
      (Startdate <= @InputDate
       and
       (StopDate >= @InputDate or StopDate IS NULL)
       and
       SupplierID = @InputGUID)
                       
or
      
       (Startdate <= @InputDate
        and
        @InputDate >= (SELECT DISTINCT MAX(SecDate) FROM tmtB WHERE SecDate <= @InputDate)
         and
         SupplierID = @InputGUID)
                       
                        
ORDER BY CustomerNo



My tmtB_tables contains the following:

CustomerNo        SupplierID     StartDate     StopDate
1695                          2              2001-01-01  2001-02-01
1695                          2              2010-07-01  2010-07-31


Inputparameters when I execute in Studio Management

@InputeDate = 2011-01-01
@InputGUID = 2

When I execute my query it returns both records, but I only want the line with stopDate nearest to @Inputdate. By that I mean that record with the "biggest" StopDate.

Can someone tell me what I do wrong?


Comment
Watch Question

Author

Commented:
Sorry the query should be like this:

SELECT distinct
CustomerNo,
StartDate,
StopDate,
SupplierID
                                                                       
FROM tmtB

WHERE
      (Startdate <= @InputDate
       and
       (StopDate >= @InputDate or StopDate IS NULL)
       and
       SupplierID = @InputGUID)
                       
or
     
       (Startdate <= @InputDate
        and
        @InputDate >= (SELECT DISTINCT MAX(StopDate) FROM tmtB WHERE StopDate<= @InputDate)
         and
         SupplierID = @InputGUID)
                       
                       
ORDER BY CustomerNo
Ephraim WangoyaSoftware Engineer
CERTIFIED EXPERT

Commented:

Thats because the dates are distinct

Author

Commented:
Hi Ewan,

Could you be a bit more specific? I am not a shark to SQL :)
StartDate and StopDate are timestamps and therefor would always be distinct
you could use
DATEPART(year, StartDate)
,DATEPART(month, StartDate)
,DATEPART(day,StartDate)
Ephraim WangoyaSoftware Engineer
CERTIFIED EXPERT

Commented:
I meant to say the dates are not distinct

Try this
;with CTE
as
(
	SELECT
	CustomerNo,
	StartDate,
	StopDate,
	SupplierID,
	ROW_NUMBER() OVER (PARTITION by CustomerNo order by StopDate desc) RN
	FROM tmtB 
	WHERE (Startdate <= @InputDate
		   and
		   (StopDate >= @InputDate or StopDate IS NULL)
		   and
		   SupplierID = @InputGUID)
)

select * 
from CTE A
where A.RN = 1

Open in new window

Software Engineer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Okay, that works with the 2 records Ewan! But lets say I insert a record more with the following:

CustomerNo        SupplierID     StartDate     StopDate
1695                          2              2001-04-01  IS NULL

and set the input parameters to

@InputeDate = 2011-04-01
@InputGUID = 2

When executed it returns:

CustomerNo        SupplierID     StartDate     StopDate
1695                          2              2010-07-01  2010-07-31

which is not correct it should return:

CustomerNo        SupplierID     StartDate     StopDate
1695                          2              2001-04-01  IS NULL

cause of:
WHERE (Startdate <= @InputDate and (StopDate >= @InputDate or StopDate IS NULL) and SupplierID = @InputGUID)
SharathData Engineer
CERTIFIED EXPERT

Commented:
try this query.
SELECT CustomerNo, StartDate, StopDate, SupplierID 
FROM   (SELECT *, 
               ROW_NUMBER() 
                 OVER(PARTITION BY CustomerNo ORDER BY StopDateNull DESC, StopDate DESC) rn 
        FROM   (SELECT *, 
                       CASE 
                         WHEN StopDate IS NULL THEN 1 
                         ELSE 0 
                       END StopDateNull 
                FROM   tmtB_tables 
                WHERE  SupplierID = @InputGUID 
                       AND StartDate <= @InputDate) t1) t2 
WHERE  rn = 1

Open in new window

tested with your sample data.
DECLARE  @tmtB_tables  TABLE( 
                             CustomerNo INT, 
                             SupplierID INT, 
                             StartDate  DATE, 
                             StopDate   DATE 
                             ) 

INSERT @tmtB_tables 
VALUES(1695,2,'2001-01-01','2001-02-01'), 
      (1695,2,'2010-07-01','2010-07-31'), 
      (1695,2,'2001-04-01',NULL) 

DECLARE  @InputDate DATE, 
         @InputGUID INT 

SELECT @InputDate = '2011-01-01', 
       @InputGUID = 2 

SELECT CustomerNo, StartDate, StopDate, SupplierID 
FROM   (SELECT *, 
               ROW_NUMBER() 
                 OVER(PARTITION BY CustomerNo ORDER BY StopDateNull DESC, StopDate DESC) rn 
        FROM   (SELECT *, 
                       CASE 
                         WHEN StopDate IS NULL THEN 1 
                         ELSE 0 
                       END StopDateNull 
                FROM   @tmtB_tables 
                WHERE  SupplierID = @InputGUID 
                       AND StartDate <= @InputDate) t1) t2 
WHERE  rn = 1
/*
CustomerNo	StartDate	StopDate	SupplierID
1695	2001-04-01	NULL	2
*/

Open in new window

Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
CERTIFIED EXPERT

Commented:
Distinct only work if you whole row is distinct otherwise not.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.