consolidate 3 queries down to 1, possible in my situation?

[complexx]

In order to find the most recent piece of data for a particular customer I need to query my table in 3 iterations.

I have a TABLE with data in it pertaining to all customers.

QUERY 1 orders all the records in TABLE by date.

QUERY 2 plucks all records related to specified customer from QUERY 1.

QUERY 3 selects the TOP 1 from QUERY 2.

My main concern is that this all happens automatically. Can I shorten this process at all?

 

[pbaldy]

I would think it could be done with one query:

SELECT TOP 1 ...
FROM ...
WHERE CustomerField = Whatever
ORDER BY DateField

 

[complexx]

I realize I forgot to mention that you could have multiple data on the same date for one customer.

How could you add the RECORDID to the select statement you posted above so it selects the RECORDID that has the greatest value in addition to matching the Customer name and date.

 

[pbaldy]

If you're saying that you'd want to select the highest RECORDID if there are multiple records for a date, add it to the ORDER BY clause.

 

[Moniker]

You could also do a Max function to get the highest RECORDID, like so:

SELECT Max(RECORDID), Field2, Field3... etc.

~Moniker