Select distinct not working

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?


MadsingAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Ephraim WangoyaConnect With a Mentor Commented:
I missed the OR part
;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)
    or   (Startdate <= @InputDate and @InputDate >= 
              (SELECT DISTINCT MAX(StopDate) 
               FROM tmtB 
               WHERE StopDate<= @InputDate) and SupplierID = @InputGUID)
)

select * from CTE A
where A.RN = 1
order by A.CustomerNo

Open in new window

0
 
MadsingAuthor 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
0
 
Ephraim WangoyaCommented:

Thats because the dates are distinct
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
MadsingAuthor Commented:
Hi Ewan,

Could you be a bit more specific? I am not a shark to SQL :)
0
 
cfEngineersCommented:
StartDate and StopDate are timestamps and therefor would always be distinct
you could use
DATEPART(year, StartDate)
,DATEPART(month, StartDate)
,DATEPART(day,StartDate)
0
 
Ephraim WangoyaCommented:
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

0
 
MadsingAuthor 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)
0
 
SharathData EngineerCommented:
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

0
 
Alpesh PatelAssistant ConsultantCommented:
Distinct only work if you whole row is distinct otherwise not.
0
All Courses

From novice to tech pro — start learning today.