DISTINCT RECORDS WITHIN A GROUP

[jobillborf]

Currently using version 11. the SQL in the report is as follows:

SELECT `301data`.`SegmentID`, `301data`.`PC`, `301data`.`PROBDTTM`, `301data`.`RESCODE`
FROM `QUEENSJOINFINAL` `301data`
WHERE (`301data`.`PC`='SA' OR `301data`.`PC`='SA1')
ORDER BY `301data`.`SegmentID`

Note: I do not use sql in crystal. Please explain how to do this task with a formula or function.

The report is grouped by SegmentID. Within the grouping is the field PROBDTTM (DATE TIME FIELD).

I would like to see distinct records by the date. In other words within the group I would like to see that date once


35212 7

35212 SA SSINF 6/21/2007 0:00
35212 SA SINSP 6/1/2007 0:00
35212 SA SINSP 6/21/2007 0:00
35212 SA SINSP 6/27/2007 0:00
35212 SA1 SSINF 7/6/2007 0:00
35212 SA SINSP 6/4/2007 0:00
35212 SA SSINF 6/27/2007 0:00

There should be only five records in the group with one record for 6/21/07 ommitted and one record for 6/27/07 omitted.

thank you

 

[PatriciaObreja]

Right click a field from Details section > Format Field > Common tab > check Suppress if duplicated.

-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium

 

[lbass]

Insert a second group on datetime and then place the fields in the group header or footer. But I would think it would matter which records you omit, e.g., maybe you'd want to order by one of the fields so that you know what you'd be omitting. In the future, it would help if you labelled your columns.

-LB