Re: Can it be done in SQL

Toby Denbow wrote: > This is exactly what I was looking for. > What does the "where y.itemid <= x.itemid" clause really mean in the > following insert? Why does it work? ... > >insert rca_detail > > select x.ordnum, count(*), x.itemid, x.qty, x.amt > > from #detail x, > > #detail y > > where y.itemid <= x.itemid > > and y.ordnum = x.ordnum > > group by x.ordnum, x.itemid It works because you are joining the table to itself, and grouping by ordnum and itemid. the 'where y.itemid <= x.itemid' clause will cause the count(*) to represent the count of rows that satisfy this condition. So, given unique itemids (this is necessary), you can see that the lowest itemid will have exactly 1 row with an itemid that is equal or less than itself, the next lowest itemid will have exactly 2 rows that are <= itself, and so on. The 'and y.ordnum = x.ordnum' clause is only necessary if your detail source has more than one order. To answer your other question (posted separately) I learned this type of stuff from a lecture at a Sybase conference several years ago. The lecture was given by a Professor David Rozenshtein, PH.D. who is Professor of Computer Science at Long Island University. HTH, and am glad to contribute to a newsgroup which has proven quite helpful to me in the past! Rusty -------------------------------------------------- Rusty Alderson, RCA Client/Server Solutions: Sybase DBA, DB-Library Development, C/C++, MFC Powerbuilder Development, Project Management --------------------------------------------------