I agree with David's remarks concerning the set oriented nature of SQL.
This particular numbering problem may very well best be handled at the
client.

BTW, I'm not sure, however, that SQL Server is "purer" than Oracle ;-)

That having been said, however, it is not necessary to use a cursor in this
situation.  I suppose it is a matter of taste whether the solution is
"messy", and its performance may be problematical, but that exercise is left
to the reader...

The issue here is that there must be some way to inherently order each
subset of boxes for a given shipper.  That is, in order to assign 1 of, 2
of, etc. there must be some way to identify which one is first, second, etc.
You haven't given a lot of detail as to what the columns in your table mean,
but I will assume that each entry in your table is assigned a unique
'lngCargoID' (whose idea was it to use a form of Hungarian notation for
column names, anyway? - you want to talk about messy :-).  Thus we will
arbitrarily assign the first box in a shipper's subset to the box with the
lowest ID, the second to the next lowest, etc.  If this isn't what your
situation is, you will have to identify one or more columns in a subset
which would be the basis for an appropriate ordering.

Anyway:

	SELECT *,
	  (SELECT COUNT(*) FROM tblCargoTranx T2
		WHERE T2.lngCargoID<=T1.lngCargoID
		AND T2.lngShipperID=T1.lngShipperID) as BoxCount + ' of ' +
	  (SELECT COUNT(*) FROM tblCargoTranx T3
     		WHERE T3.lngShipperID = T1.lngShipperID) as Boxes
	FROM tblCargoTranx T1;

The idea is that each set of boxes for a given 'lngShipperID' is partitioned
into groups of subsets where each subset contains all the members with a
lower 'lngCargoID' than the current ID, and we just count the number of
elements in these subsets.

Not tested, since I have no data, but hopefully this gives you an idea of
something to work with.

--
Jeff Mason              Custom Apps, Inc.
Jeff@c...

-----Original Message-----
From: David Cameron [mailto:dcameron@i...]
Sent: Sunday, February 02, 2003 10:14 PM
To: sql language
Subject: [sql_language] RE: Quantity Query


SQL is about dealing with sets. Rows are identified by the data they hold,
not where the appear in the resultset. So the idea of numbering things,
which is basically what want, goes against the theory of how SQL should
work. Oracle has some functions to do this kind of thing, but SQL Server is
more "pure".

To do it entirely in SQL would involve a cursor or something equally messy.
In this it would be particularly messy as you want the numbering on a
subset. This is essentially a problem to be solved with procedural code,
which is best performed on the client.

regards
David Cameron
nOw.b2b
dcameron@i...

> -----Original Message-----
> From: Enzo Zaragoza [mailto:enzaux@g...]
> Sent: Monday, 3 February 2003 2:21 PM
> To: sql language
> Subject: [sql_language] RE: Quantity Query
>
>
>
> 	thanks for the quick response! I thought is it possible
> to do it all in the query itself (1 of 2)
> but thanks for the idea!  i guess i need to put some little
> coding on my forms and reports to make it.
>
> Thanks,
>
> enzo c",)
>
> -----Original Message-----
> From: David Cameron [mailto:dcameron@i...]
> Sent: Monday, February 03, 2003 10:54 AM
> To: sql language
> Subject: [sql_language] RE: Quantity Query
>
>
> I think you would need a subquery for this:
>
> SELECT <fields>,
>     (SELECT COUNT(*)
>     FROM tblCargoTranx sCT
>     WHERE sCT.lngShipperID = CT.lngShipperID) AS Boxes
> FROM tblCargoTranx CT
>
> A faster way might be:
> SELECT <fields>,
>     sCT.Boxes
> FROM tblCargoTranx CT
>     INNER JOIN (SELECT COUNT(*) Boxes
>                FROM tblCargoTranx
>                GROUP BY lngShipperID) sCT ON
>     sCT.lngShipperID = CT.lngShipperID
>
> Which one is faster depends on the query optimizer.
>
> Then assign the numbers (eg 1 of n) client side.
>
> regards
> David Cameron
> nOw.b2b
> dcameron@i...
>
> > -----Original Message-----
> > From: Enzo Zaragoza [mailto:enzaux@g...]
> > Sent: Monday, 3 February 2003 1:12 PM
> > To: sql language
> > Subject: [sql_language] Quantity Query
> >
> >
> >
> > 	I have this cargo system.  For every cargo batch(1
> > container) there is a case that a single shipper owns 2 or
> more boxes.
> > Now what I want to do is query the cargo table and have boxes
> > of the same shipper be grouped and have it labeled
> > as 1 of 2, 2 of 2 (if there are two boxes owned by a single
> > shipper).  How can I create a query that would
> > give me this results.
> >
> > My table structure is somehow like this:
> >
> > tblCargoTranx
> > --------------
> > lngCargoID
> > intBoxType
> > lngShipperID
> > .
> > .
> > and so on
> >
> > Thanks,
> >
> > enzo c",)
> >
> >
> >
> >
>
>
>
>
>
>