CODEWITHSUNDEEP
sqlsyntaxfirebird
JonBravo
JonBravo

Reputation: 27

SQL DISTINCT/GROUP BY not working

Bear with me here, I am very new to SQL. I have the following query written out but the DISTINCT is essentially being negated by my WHERE statement. This is due to the fact that the dates in the table are followed by a time stamp so if there is any difference in the time the line items were put it, it generates a new row.

What would be the best way to have it completely ignore everything after the date so that no new lines are generated due to time stamps? From what I have found, I think this is better achieved using GROUP BY but that seams to always generate an error (most likely me not knowing where to put the aggregate function).

Any help would be greatly appreciated. Thank you!

Select DISTINCT
 so.num AS Ref
,  so.shiptoname AS Recipient_Full_Name
, so.shiptoaddress AS Address_1
, so.shiptocity AS City
, stateconst.name AS State
, so.shiptozip AS Zip
, so.billtoname AS Buyer_Name
, qbclass.name AS Class
, soitem.datescheduledfulfillment AS Fulfillment_Date
From SO
JOIN stateconst
ON so.shiptostateid=stateconst.id
JOIN qbclass
ON so.qbclassid=qbclass.id
JOIN soitem
ON so.id=soitem.soid
WHERE DATESCHEDULEDFULFILLMENT LIKE '2016-05-10%'

Upvotes: 1

Views: 112

Answers (2)

Aaron Dietz
Aaron Dietz

Reputation: 10277

Use CAST() to change the datetime data type to a date type:

Select DISTINCT
 so.num AS Ref
,  so.shiptoname AS Recipient_Full_Name
, so.shiptoaddress AS Address_1
, so.shiptocity AS City
, stateconst.name AS State
, so.shiptozip AS Zip
, so.billtoname AS Buyer_Name
, qbclass.name AS Class
, CAST(soitem.datescheduledfulfillment as date) AS Fulfillment_Date
From SO
JOIN stateconst
ON so.shiptostateid=stateconst.id
JOIN qbclass
ON so.qbclassid=qbclass.id
JOIN soitem
ON so.id=soitem.soid
WHERE CAST(soitem.datescheduledfulfillment as date) = '2016-05-10'

Upvotes: 3

Sturgus
Sturgus

Reputation: 686

Do you actually need the date in your query output? Because leaving it out would solve your 'problem':

Select DISTINCT
 so.num AS Ref
,  so.shiptoname AS Recipient_Full_Name
, so.shiptoaddress AS Address_1
, so.shiptocity AS City
, stateconst.name AS State
, so.shiptozip AS Zip
, so.billtoname AS Buyer_Name
, qbclass.name AS Class

From SO
JOIN stateconst
ON so.shiptostateid=stateconst.id
JOIN qbclass
ON so.qbclassid=qbclass.id
JOIN soitem
ON so.id=soitem.soid
WHERE DATESCHEDULEDFULFILLMENT LIKE '2016-05-10%'

Upvotes: 0

Related Questions