[RESOLVED] Ideas on correcting this SQL statement...

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.

First, I'm surprised it isn't throwing an error. You're returning price but not using it in an aggregate (at least initially) and it isn't part of the group by.

At any rate, the reason you get more than one row is because you're grouping (or should be) by COLOR and PRICE... To get what you want though, you'll need to add in an inner select that get the max date then join that back to the table... something like this:

Code:

---

select *
from products P
inner join (select products.ID, max(products.SomeDate) as MDate
              from products group by products.ID) D
  on P.ID = D.ID and P.SomeDate = D.MDate

---

The inner select gets the max date for each product.
That result is then joined back to the original table. As long as the ID/Date combination is unique, you'll get the most recent record for each product.

Clearly, adjust as needed for your needs & table structure.

-tg

Thanks techgnome, once again you've helped me out.

Yes, that first statement does throw an error, sorry, I abbreviated most of the statement for brevity, and introduced the error then... only had it working with the second statement that included the AVG function..

I greatly appreciate it, I could have spent days on it as I'm not very familiar with inner joins.

I've adjusted my code as per your suggestions, and it works beautifully.

The only issue now is (gasp!) duplicate rows in the database, which the new code (faithfully and accurately) returns.

I'm sure I could modify it a bit to include some distinct statement, but I guess I should just remove the duplicated rows on a regular basis, as they shouldnt be there in the first place.

These rows still get added because of my poor coding knowledge, data validation, etc...when I originally wrote the data input side of the project years ago, and I haven't updated/fixed it yet.

For now, I just occasionally run a procedure to delete said duplicates from the database... too many things to work on, and its a personal, non commercial project, and I'm aware of the flaw, so no heads will roll because of it.. :)

Anyway. Thanks again techgnome.
I appreciate the rapid, succinct and accurate response.
josh