Is there a better sub query??

nande12 (Programmer)

(OP)

5 Aug 08 10:50

Hello-

Does anyone know of a better way?

DATA
====================================
COMPRT                  DATE          QTY   PARENT
91879                      7/8/2008    1       92614
91880                      7/8/2008    1       92614
92616                      7/8/2008    0       92614
92616                      2/7/2008    1       92614
92628                      2/8/2008    1       92614

I want to select unique COMPRT where DATE is max and QTY is  not 0 as show in the following:

====================================
COMPRT                  DATE          QTY   PARENT
91879                      7/8/2008    1       92614
91880                      7/8/2008    1       92614
92628                      2/8/2008    1       92614

Here is my query.

CODE

SELECT p1.COMPRT, p1.QTY
    FROM DATA p1
    WHERE p1.DATE IN
        (
            SELECT max(p2.DATE)
            FROM DATA p2
            WHERE p2.PARENT =  '92614'
            GROUP BY p2.COMPRT
        )
    AND p1.PARENT =  '92614'
    AND p1.QTY > 0
    ORDER BY p1.COMPRT

The problem is that I would like to get the data into excel using a cell value for PARENT but having PARENT in two places doesn't seem to work. I get a message saying " SQLBindParameter has not been called for parameters #2." when using PARENT = [?] in both locations.

Pervasive v8

Thanks,
Nate

Join Tek-Tips^® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Come Join Us!

Posting Guidelines

Is there a better sub query??