Keeping a ROW intact when using MAX

[wickyd]

Hi folx

I want to use:

SELECT NOCOLOUR, GRVQTY, MAX(CAST(GRVDOCNO AS INTEGER)) AS LATESTGRV INTO RESULT3 FROM RESULT2
GROUP BY NOCOLOUR
ORDER BY NOCOLOUR ASCENDING;

but this is not entirely correct as it causes the following problem:

The test data is the following:

NOCOLOUR   GRVQTY   GRVDOCNO
A            10         1020
B            20         1012
B            50         1034
B            40         1065

The result with my SQL is:

NOCOLOUR   GRVQTY  LATESTGRV
A            10         1020
B            20         1065 <-- GRVQTY is nonsense

I want the result to be:

NOCOLOUR   GRVQTY  LATESTGRV
A            10         1020
B            40         1065 <-- the row must remain intact

Thank you.

 

[JarlH]

The result you get is very odd, because your SQL statement is illegal. GRVQTY must either be grouped by, or used in a set function (like MAX).

An ANSI SQL compliant solution to your problem:

SELECT * FROM result2
WHERE (nocolour,GRVDOCNO) in
(select NOCOLOUR,max(GRVDOCNO) from result2
group by NOCOLOUR)

ANSI SQL-99, using the non-core feature F641, "Row and table constructors".

Or, if your database server doesn't support row constructors:

select * from result2 r
where GRVDOCNO = (select max(GRVDOCNO) from result2 r2
where r.NOCOLOUR = r2.NOCOLOUR)

Core SQL-99 compliant.

 

[wickyd]

I am using a report writer called QRDesign which is based on the DBISAM engine.

Unfortunately, neither of your solutions work.

I modified my previous SQL statement, which now results in a different test data table, whereby GRVDOCNO is now descending, using:

SELECT GRVDOCNO, SUM(QTY)GRVQTY, NOCOLOUR INTO RESULT2 FROM RESULT1
GROUP BY GRVDOCNO, NOCOLOUR
ORDER BY NOCOLOUR ASCENDING, GRVDOCNO DESCENDING;

I used the following SQL statement to get to the correct result:

SELECT * INTO RESULT3 FROM RESULT2
GROUP BY NOCOLOUR;

JarlH, thank you for the help.