Hello all.
I'm using VB6 to access a MS SQL 2008 database.

I have a SQL statement that I thought achieved what I needed it to do, but realise I need to tweak it a bit so it works correctly.
I've had a look at it over the last few days but I'm not sure of the best way of doing it.

This statement is generated using vb6 code, and I have omitted most of the where clauses for brevity...

Furthermore, I'm using zeros in the select statement so that the returned recordset always has the same number of columns, which makes life easier when I process the returned records.

The statement is:
Code:
SELECT 0, 0, COLOUR, 0, 0, PRICE, max(DATE) FROM main.dbo.database where PRODUCTNAME = 'some product name' Group By COLOUR order by max(DATE) DESC
This statement is meant to return a recordset that, for a given product name, returns the most recent date, the most recent price, and its colour.

However, I want the recordset to ONLY return one row for each colour that is returned, and only the most recent one for that colour... and at the moment it does not.

ie, it will return:

0,0,brown,0,0,$13.50,12/09/11
0,0,grey,0,0,$14.99,11/09/11
0,0,purple,0,0,$14.99,11/09/11
0,0,brown,0,0,$14.99,09/09/11

I only want it to return one record for brown, the most recent one, with the most recent price.

I have tried to modify the statement so that it becomes:
Code:
SELECT 0, 0, COLOUR, 0, 0, avg(PRICE), max(DATE) FROM main.dbo.database where PRODUCTNAME = 'some product name'  Group By COLOUR order by max(DATE) DESC.
however, while this returns only one row per COLOUR that is returned, the price is the average price, as opposed to the most recent price.

If you have any suggestions on how to resolve this, or need me to clarify anything (its after 11pm here afterall!) please let me know.
Thank you.