11-27-2014 04:30 AM
Use the notsorted option, but be aware that this can cause false results.
11-27-2014 04:34 AM
There isn't one in that sense. first/last means a record which appears first or last in a sorted group - that being said if you know your groupings and what order would highlight first or last then you could programmatically do it. I.e. if I have a set of data by id, with a date, then I could assume that date sequential would be the order and do:
create table WANT as
case when A.DATE=B.MIN_DATE then 1
else 0 end as FIRST,
case when A.DATE=B.MAX_DATE then 1
else 0 end as LAST
from HAVE A
left join (select distinct ID,
min(DATE) as MIN_DATE,
max(DATE) as MAX_DATE
group by ID) B
However, why? Is order of your data that important? If so create a temporary variable called sort, and assign it to _n_. Sort your data and get first/last, then sort your data by the temporary variable setting it back to old sort. Again, why bother though.