Can I Use Sql 2008 View To Rearrange Table

create view TEST as
select * from mytable b where columndate =
select  max(columndate) from mytable a where a.customerid=b.customrid
group by substring(customerid, 1,6) )

Pooja Katiyar Verma:

WHERE     (gpdate =
                          (SELECT     MAX(gpdate) AS Expr1
                            FROM          dbo.tbl_rmcore_gpactv AS a
                            WHERE      (SUBSTRING(gpcustno, 1, 6) = SUBSTRING(b.gpcustno, 1, 6))
                            GROUP BY SUBSTRING(gpcustno, 1, 6)))

Your code left duplicate custno in view. Did not want that. After making above change, I still got some duplicate custno when the gpdate's were exactly the same. Also, not sure if it matters, your view took 3-5 minutes to create.


ScottPletcher:

--CREATE VIEW view_name AS
SELECT *
FROM (
    SELECT
        tn.Cust, tn.Date, tn.Comment,
    ROW_NUMBER() OVER(PARTITION BY substring(tn.Cust,1,6) ORDER BY tn.Date DESC) AS row_num
    FROM dbo.tablename tn
) AS derived
WHERE
    row_num = 1


Your code also left duplicate custno in view. After making above change all duplicates were gone and view created in a few seconds.


It was probably in the way I explained it that caused the duplicates.

Anyway, I will award points to Scott since the final results were correct for what I needed after minor change.
Pooja, even after minor change, your view still had some duplicate custno.
Also, not sure why, but Scotts view created so much faster.

After minor change ( ROW_NUMBER() OVER(PARTITION By substring(tn.Cust,1,6) ORDER BY tn.Date DESC) AS row_num )
your code did the trick.

Thanks to both of you for your help.