INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Is there a better sub query??

Is there a better sub query??

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close