SQL- Grouping variables and selecting the last row of the first group of a repeating group

Question

So I have this problem. Say I have grouped a variable already, I am trying to select the first group out repeated groups.

Table1

ProductID   Name                              Dates
----------- ---------------------------------- ---------- 
491         Paint - Silver                     1999/08/1          
491         Paint - Silver                     1999/12/23         
491         Paint - Blue                       2000/1/1         
491         Paint - Silver                     2001/1/1       
491         Paint - Red                        2001/1/21          
491         Paint - Red                        2001/1/25         
491         Paint - Silver                     2002/3/4         
491         Paint - Blue                       2003/4/14          
491         Paint - Blue                       2003/4/15         
491         Paint - Blue                       2004/3/26         
491         Paint - Red                        2005/2/1         
491         Paint - Red                        2005/5/1         
491         Paint - Yellow                     2006/7/2         
491         Paint - Yellow                     2009/1/1        
491         Paint - Black                      2010/1/1        
491         Paint - Black                      2011/1/3

The problem is, I want to select Paint - Silver when before it changes to Paint - Blue hence the date will be 1999/12/23 for Paint - Silver. In a nutshell I want the the last date of the first product before it got changed to another product for productID = 491. This just for one productID (I.e 491). I want to do this for multiple productID's that all have a similar format. Nothing seems to work and any help at all will be appreciated

Answer 1

This is the query which you should point out the change in product name.

SELECT a.name, b.name, a.dates, b.dates , (a.row - b.row) as row_diff
FROM 
(SELECT *, @rowa := @rowa + 1 AS row FROM product 
        CROSS JOIN (SELECT @rowa := 0) AS r) a 
LEFT JOIN 
(SELECT *, @rowb := @rowb + 1 AS row FROM product 
        CROSS JOIN (SELECT @rowb := 0) AS r) b 
ON a.dates < b.dates 
WHERE a.name != b.name AND (a.row - b.row) = -1;

This will give the following resultset :

+--------------+--------------+------------+------------+----------+
| name         | name         | dates      | dates      | row_diff |
+--------------+--------------+------------+------------+----------+
| Paint-Silver | Paint-Blue   | 1999-12-23 | 2000-01-01 |       -1 |
| Paint-Blue   | Paint-Silver | 2000-01-01 | 2001-01-01 |       -1 |
| Paint-Silver | Paint-Red    | 2001-01-01 | 2001-01-21 |       -1 |
| Paint-Red    | Paint-Silver | 2001-01-25 | 2002-03-04 |       -1 |
| Paint-Silver | Paint-Blue   | 2002-03-04 | 2003-04-14 |       -1 |
| Paint-Blue   | Paint-Red    | 2004-03-26 | 2005-02-01 |       -1 |
| Paint-Red    | Paint-Yellow | 2005-05-01 | 2006-07-02 |       -1 |
| Paint-Yellow | Paint-Black  | 2009-01-01 | 2010-01-01 |       -1 |
+--------------+--------------+------------+------------+----------+

Answer 2

I'm not versed in sql, but couldn't you loop through the name list comparing each row to the one below and looking for a change to a different name (or a specified name, eg paint blue) and at then select the current row if a difference is found?