Re: A SQL question.

Not a very elegant solution, but if the number of distinct values the column Type contains is small and well-known, then you can do the following:

select rownum,type,codeno,price from table where type='A1' union all
select rownum,type,codeno,price from table where type='A2' union all
select rownum,type,codeno,price from table where type='A3' ...
order by type,codeno;

Hope this helps,

Nuno Guerreiro

On Fri, 18 Sep 1998 14:14:43 GMT, ancient_97_at_yahoo.com (Zhang Yiquan) wrote:

>Hi all.
>
>I would appreciate any help available on this problem.
>
>I have to print some data as a report using an SQL statement.
>It's a line-item and control break kind of report.
>For example I have these...
>
>Type CodeNo Price
>A2 0912 14.00
>A2 0913 16.00
>A2 0914 15.00
>A4 1150 25.00
>A2 0916 18.00
>A4 1152 29.00
>A4 1153 29.00
>
>How would I make a query so that I can get the results returned as
>sorted by Type and then CodeNo with a running serial count for each
>Type? For example....
>
>
>Report Title
>
>Count Type CodeNo Price
>1 A2 0912 14.00
>2 A2 0913 16.00
>3 A2 0914 15.00
>4 A2 0916 18.00
>1 A4 1150 25.00
>2 A4 1152 29.00
>3 A4 1153 29.00
>
>
>The thing here is HOW to produce that Count column that would reset at
>every change in Type? I've tried grouping and ordering and even
>rownum, but just cannot get it to reset to 1 at every change in Type.
>
>Thanks in advance!
>
>
>
Received on Fri Sep 18 1998 - 10:29:22 CDT