Hi,

Actually to add to my last post, I have a requirement as follows:

                              IsFirst?    IsLast?

Group 1   Rec 1        T               F
Group 1   Rec 2        F               F
Group 1   Rec 3        F               T
Group 2   Rec 1        T               F
Group 2   Rec 2        F               F
Group 2   Rec 3        F               T
Group 3   Rec 1        T               F
Group 3   Rec 2        F               T
Group 4   Rec 1        T               T

Basically we are doing an export and need to prefix the first record of a
group with some text, and suffix the last record of a group with some text.
Because ReportBuilder doesnt allow you to count the number of records in a
group, I need to be able to have the resultant SQL result have that
information available.  Is there a way to do this (multiple SQL statements
are fine)?

Thanks

Alex

Alex,

<< Basically we are doing an export and need to prefix the first record of a
group with some text, and suffix the last record of a group with some text.
Because ReportBuilder doesnt allow you to count the number of records in a
group, I need to be able to have the resultant SQL result have that
information available.  Is there a way to do this (multiple SQL statements
are fine)?  >>

The first record in the group is easy (with multiple statements), however
the last record in the group may be problematic.   I can't think of any way
to determine the last record in the group except navigationally.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Al Vas" <forgetit@forgetit.com> wrote in message
news:E7D67DA6-4691-4013-B898-1F643AED0004@news.elevatesoft.com...
> Hi,
>

Try something like this (not tested). If you ONLY want first and last, add
the corresponding where clause (t2.id not = null or t3.id not = null) to the
last select.

Robert

SELECT MAX(ID) DABIG, ID, GROUPNUM INTO MEMORY\BIG FROM TABLE GROUP BY ID,
GROUPNUM;
SELECT MIN(ID) DATINY, ID, GROUPNUM INTO MEMORY\SMALL FROM TABLE GROUP BY
ID, GROUPNUM;
SELECT T1.ID, IF(T2.ID IS NULL THAN 'F' ELSE 'T') ISHIGH, IF (T3.ID IS NULL
THEN 'F' ELSE 'T') ISLOW
FROM TABLE T1
LEFT OUTER JOIN MEMORY\BIG T2 ON T1.ID = T2.ID
LEFT OUTER JOIN MEMORY\SMALL T3 ON T3.ID = T1.ID;

> Actually to add to my last post, I have a requirement as follows:
>
>                               IsFirst?    IsLast?
>
> Group 1   Rec 1        T               F
> Group 1   Rec 2        F               F
> Group 1   Rec 3        F               T
> Group 2   Rec 1        T               F
> Group 2   Rec 2        F               F
> Group 2   Rec 3        F               T
> Group 3   Rec 1        T               F
> Group 3   Rec 2        F               T
> Group 4   Rec 1        T               T
>
> Basically we are doing an export and need to prefix the first record of a
> group with some text, and suffix the last record of a group with some
> text. Because ReportBuilder doesnt allow you to count the number of
> records in a group, I need to be able to have the resultant SQL result
> have that information available.  Is there a way to do this (multiple SQL
> statements are fine)?
>
> Thanks
>
> Alex
>