Is there a better sub query??

nande12 · Aug 5, 2008

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

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Is there a better sub query??

nande12

Programmer

Similar threads

Part and Inventory Search

Sponsor