Help with selecting unique entries with conditions

[KurkPeterman]

I am currently having a problem getting the exact query for my problem.  I have a table that has the following columns:

 

customer, date, data1, data2, data3

I'm looking to create a query that gives me:

 

a unique customer entry

a unique date entry (where the date is the most recent entry prior than a given date)

data1, data2, and data3 (ordered by one of these columns)

 

My problem is that I can't seem to get the data ordered by the non-distinct columns.  I end up with something like this:

 

SELECT data1, data2, data3 FROM custtable WHERE date <= 04/15/2010 GROUP BY customer ORDER BY date DESC

 

The problem with the above query is that I am only able to sort by the date (in order to get only the most recent date entry) and am unable to sort by the columns that I'm actually querying for.

 

The goal of this query is to select the most recent entry prior to a given date for all unique customers and be able to sort the data in those entries.  Any help is much appreciated (I think I may need to use some HAVING or DISTINCT keywords, but I haven't been able to make it work).  Thanks!

[Siann Beck]

Does your table have a unique id field? If so, perhaps a sub-query would work for you:

 

SELECT data1, data2, data3
FROM   custtable
WHERE  recordid in (
   SELECT recordid
   FROM   custtable
   WHERE  date <= '04/15/2010'
   GROUP  BY customer
   ORDER  BY date DESC)
ORDER  BY data1, data2, data3

[KurkPeterman]

Thank you very much.  It is indeed unique and your query worked.  I'm also trying it with an inner join to see if it is faster.