Problem of order when using group by in SELECT INTO

[Shevshenko]

I use VFP6 and I have a problem of order when I create a temporary table using SELECT INTO Statement.
The table Lignebl conatains the products of an invoice and I want to select the lines using GROUP BY Lignebl.Ligart which is the productid to SUM the quantities of them.And I want the result in the same order of RECNO() In the table LIGNEBL
So my query is
SELECT LIGNEBL.LIGART , ...... , SUM(LIGNEBL.LIGQTE) AS LIGQTE;
FROM LIGNEBL ;
GROUP BY LIGNEBL.LIGART ;
INTO TABLE TMP_ART
EX : LIGNEBL
Recno(1) Prod1 Qte:2
Recno(2) Prod2 Qte:1
Recno(3) Prod2 Qte:2
Recno(4) Prod1 Qte:1
Recno(5) Prod3 Qte:4
The result in TMP_ART is
Prod2 Qte:3
Prod1 Qte:3
Prod3 Qte:4
And what I want in TMP_ART is
Prod1 Qte:3
Prod2 Qte:3
Prod3 Qte:4

 

[Mike Gagnon]

Shevshenko

Set an index on the field the contains the "Prod X" values and
SELECT LIGNEBL.LIGART , ...... , SUM(LIGNEBL.LIGQTE) AS LIGQTE;
FROM LIGNEBL ;
GROUP BY LIGNEBL.LIGART ;
INTO TABLE TMP_ART
INDEX ON LIGART TAG LIGART
SET ORDER TO TAG LIGART
Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[cslawtom]

In your case, I will use 2 select statements (may be too many but it works)

SELECT RECNO() AS nRec, LIGNEBL.*
FROM LIGNEBL ;
INTO CURSOR CsrTemp

SELECT MIN(nRec) AS nRec, LIGNEBL.LIGART , ...... , SUM(LIGNEBL.LIGQTE) AS LIGQTE;
FROM CsrTemp ;
GROUP BY LIGNEBL.LIGART ;
INTO TABLE TMP_ART ;
ORDER BY 2, 1

 

[cslawtom]

- Should be order by 1, 2
- Assume with order no.

In your case, I will use 2 select statements (may be too many but it works)

SELECT RECNO() AS nRec, LIGNEBL.*
FROM LIGNEBL ;
INTO CURSOR CsrTemp

SELECT MIN(nRec) AS nRec, LIGNEBL.ORDERNO, LIGNEBL.LIGART , ...... , SUM(LIGNEBL.LIGQTE) AS LIGQTE;
FROM CsrTemp ;
GROUP BY LIGNEBL.ORDERNO, LIGNEBL.LIGART ;
INTO TABLE TMP_ART ;
ORDER BY 2, 1, 3