View to display the latest of a similar record

Hi,

I have a view connected to two tables. One table contains a reference for picture files and the other is a list of locations these pictures are for. What I need a view to do is bring back only the latest version of the picture we have.

I have listed what I mean below, the view though should only bring back version 4 of this record and not list the others if possible.

countryCode      cityCode      storeCode      storeType      vendorCode      rc      gridCodeX      gridCodeY      blockDescription      PPath      Ver
966      02      006      S      NSL      NULL      AN53      AN53      Another Block      966-02-006-NSL-AN53-AN53-S634369194540126263.jpg      1
966      02      006      S      NSL      NULL      AN53      AN53      Another Block      966-02-006-NSL-AN53-AN53-S634369382595167646.jpg      2
966      02      006      S      NSL      NULL      AN53      AN53      Another Block      966-02-006-NSL-AN53-AN53-S634369394187887295.jpg      3
966      02      006      S      NSL      NULL      AN53      AN53      Another Block      966-02-006-NSL-AN53-AN53-S634369516523188588.jpg      4

So in this example, all the data is the same other than the ppath and ver numbers. Is there anyway to bring back just the record that says' version 4 at the end of it?? This table will be filled with thousands of records, each record could have multiple versions but I  need it to ony bring back the latest.

Any help would be much appreciated.

Ken

Sorry about the formatting, I placed a file with this question also, that may display it better. If you need anymore info, just ask.

This is the SELECT statement from MSSQL also:

SELECT TOP 1000 [countryCode]
      ,[cityCode]
      ,[storeCode]
      ,[storeType]
      ,[vendorCode]
      ,[rc]
      ,[gridCodeX]
      ,[gridCodeY]
      ,[blockDescription]
      ,[PPath]
      ,[Ver]
  FROM [T1].[dbo].[BlockLabels]
BlockView.txt
kenuk110Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ambidextrousCommented:
Hi Ken - what is the primary key on this table -- [T1].[dbo].[BlockLabels]?

Here's a sub-query example to do this, but I would ensure that the inner query has the Primary Key columns to join on.  Am I on the right track here in regards to what your question is?

SELECT a.[countryCode]
      ,a.[cityCode]
      ,a.[storeCode]
      ,a.[storeType]
      ,a.[vendorCode]
      ,a.[rc]
      ,a.[gridCodeX]
      ,a.[gridCodeY]
      ,a.[blockDescription]
      ,a.[PPath]
      ,a.[Ver]
FROM [T1].[dbo].[BlockLabels] a
JOIN (SELECT
         x.[countryCode]
        ,x.[cityCode]
        ,x.[storeCode]
        ,x.[storeType]
        ,x.[vendorCode]
        ,x.[rc]
        ,Max(x.[Ver]) as MaxVer
      FROM [T1].[dbo].[BlockLabels] x
      GROUP BY 
         x.[countryCode]
        ,x.[cityCode]
        ,x.[storeCode]
        ,x.[storeType]
        ,x.[vendorCode]
        ,x.[rc]) b
ON a.[countryCode] = b.[countryCode]
   a.[cityCode] = b.[cityCode]
   a.[storeCode] = b.[storeCode]
   a.[storeType] = b.[storeType]
   a.[vendorCode] = b.[vendorCode]
   a.[rc] = b.[rc]

Open in new window

kenuk110Author Commented:
Hi,

Thanks for your response! I tried to copy this in to a new view but I get an 'SQL Execution Error. Error message: Incorrect syntax near 'a'.

Not sure if I have to do something more with the code you sent me??

BlockLabels is a view from two other tables, the PK in Block is called pk and the pk on the Planogram table is called ID. Is this what you mean??

ambidextrousCommented:
I think it must be a long day already...  I forgot the "AND" statements on the JOIN.  Does this work better?  I don't have the table imported so I haven't tried testing this at all.

----

SELECT a.[countryCode]
      ,a.[cityCode]
      ,a.[storeCode]
      ,a.[storeType]
      ,a.[vendorCode]
      ,a.[rc]
      ,a.[gridCodeX]
      ,a.[gridCodeY]
      ,a.[blockDescription]
      ,a.[PPath]
      ,a.[Ver]
FROM [T1].[dbo].[BlockLabels] a
JOIN (SELECT
         x.[countryCode]
        ,x.[cityCode]
        ,x.[storeCode]
        ,x.[storeType]
        ,x.[vendorCode]
        ,x.[rc]
        ,Max(x.[Ver]) as MaxVer
      FROM [T1].[dbo].[BlockLabels] x
      GROUP BY 
         x.[countryCode]
        ,x.[cityCode]
        ,x.[storeCode]
        ,x.[storeType]
        ,x.[vendorCode]
        ,x.[rc]) b
ON a.[countryCode] = b.[countryCode] AND
   a.[cityCode] = b.[cityCode] AND
   a.[storeCode] = b.[storeCode] AND
   a.[storeType] = b.[storeType] AND
   a.[vendorCode] = b.[vendorCode] AND
   a.[rc] = b.[rc] 

Open in new window

Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

ambidextrousCommented:
Regarding the second comment on the primary keys.  Is it possible you can include the SQL that the view currently contains?  It would be best to create a single query off the 2 source tables rather than creating a "view of a view".
kenuk110Author Commented:
Hi again,

Well it doesn't error anymore but it also doesn't bring back any results, I've tried changing it to bring all records back from 'a' which then brings them all back or 'b' then it brings back some null entries I have. Any ideas??

kenuk110Author Commented:
Hi,

Yeah that would be better, let me get the SQL!!

Thanks
kenuk110Author Commented:
This is the SQL that makes up the view BlockLabels:

SELECT     dbo.block.countryCode, dbo.block.cityCode, dbo.block.storeCode, dbo.block.storeType, dbo.block.vendorCode, dbo.block.rc, dbo.block.gridCodeX,
                      dbo.block.gridCodeY, dbo.block.blockDescription, dbo.Planogram.PPath, dbo.Planogram.Ver
FROM         dbo.block LEFT OUTER JOIN
                      dbo.Planogram ON dbo.block.storeType = dbo.Planogram.StoreType AND dbo.block.vendorCode = dbo.Planogram.PVendor AND
                      dbo.block.countryCode = dbo.Planogram.Country AND dbo.block.cityCode = dbo.Planogram.PLocation AND dbo.block.storeCode = dbo.Planogram.Store AND
                      dbo.block.gridCodeX = dbo.Planogram.BlockNo


Hope you can read this
ambidextrousCommented:
Thanks Ken... working on this now.
SharathData EngineerCommented:
try like this.
SELECT [countryCode], 
       [cityCode], 
       [storeCode], 
       [storeType], 
       [vendorCode], 
       [rc], 
       [gridCodeX], 
       [gridCodeY], 
       [blockDescription], 
       [PPath], 
       [Ver] 
  FROM (SELECT [countryCode], 
               [cityCode], 
               [storeCode], 
               [storeType], 
               [vendorCode], 
               [rc], 
               [gridCodeX], 
               [gridCodeY], 
               [blockDescription], 
               [PPath], 
               [Ver], 
               ROW_NUMBER() 
                 OVER(PARTITION BY [countryCode],[cityCode],[storeCode],[storeType],[vendorCode],[rc],[gridCodeX],[gridCodeY] ORDER BY Ver DESC) AS rn
          FROM [T1].[dbo].[BlockLabels]) AS t1 
 WHERE rn = 1

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kenuk110Author Commented:
BRILLIANT!!!!! It works!!! Thank you so much for this, I can't thank you enough.

Cheers,

Ken

ambidextrousCommented:
Sharath - nice code... I did not think about using the Row_Number() code.  Very nice.  I have adapted slightly to use the source tables.

Ken - the query above and the query I am sending should yield the same results (assuming I don't have any mistyped code).  The query I am sending will just hit the Source tables directly instead of a view to a view.

Hope either of these work for you!

SELECT
  a.countryCode
 ,a.cityCode
 ,a.storeCode
 ,a.storeType
 ,a.vendorCode
 ,a.rc
 ,a.gridCodeX
 ,a.gridCodeY
 ,a.blockDescription
 ,b.PPath
 ,b.Ver
FROM
  dbo.Block a 
LEFT OUTER JOIN 
 (
  SELECT q.StoreType
        ,q.PVendor
        ,q.Country 
        ,q.PLocation
        ,q.Store
        ,q.BlockNo
        ,q.PPath
        ,q.Ver
    FROM (SELECT 
             r.StoreType
            ,r.PVendor
            ,r.Country 
            ,r.PLocation
            ,r.Store
            ,r.BlockNo
            ,r.PPath
            ,r.Ver
            ,ROW_NUMBER() OVER(PARTITION BY r.StoreType, r.PVendor, r.Country, r.PLocation, r.Store, r.BlockNo, r.PPath, r.Ver ORDER BY r.Ver DESC) AS rn
            FROM [T1].[dbo].PlanoGram) AS q
   WHERE p.rn = 1
  ) p
ON a.storeType = p.StoreType AND
   a.vendorCode = p.PVendor AND
   a.countryCode = p.Country AND
   a.cityCode = p.PLocation AND
   a.storeCode = p.Store AND
   a.gridCodeX = p.BlockNo

Open in new window

kenuk110Author Commented:
Hi ambidextrous,

Thanks for your response too, I tried that but I get an error: The multi-part identifier "r.StoreType" could not be bound.

Then it lists a load of parameters...  r.PVendor....r.Country

I looked through the reference headings but I can't see any that are mis spelt .

ambidextrousCommented:
arghh!!!  

sorry for complicating this with my typos.

On Line #35 I believe it should read like this....

          FROM [T1].[dbo].PlanoGram r) q

kenuk110Author Commented:
Hi ambidextrous,

Sorry for the delay in replying, I tried to rply last night but Experts-Exchange were doing some maintenance so I couldn't get in.

When I try the code again with the changes I get:

The multi-part identifier "p.rn" could not be bound and same with p.PPath and b.Ver?
kenuk110Author Commented:
This is how it is now...
SELECT
  a.countryCode
 ,a.cityCode
 ,a.storeCode
 ,a.storeType
 ,a.vendorCode
 ,a.rc
 ,a.gridCodeX
 ,a.gridCodeY
 ,a.blockDescription
 ,b.PPath
 ,b.Ver
FROM
  dbo.Block a 
LEFT OUTER JOIN 
 (
  SELECT q.StoreType
        ,q.PVendor
        ,q.Country 
        ,q.PLocation
        ,q.Store
        ,q.BlockNo
        ,q.PPath
        ,q.Ver
    FROM (SELECT 
             r.StoreType
            ,r.PVendor
            ,r.Country 
            ,r.PLocation
            ,r.Store
            ,r.BlockNo
            ,r.PPath
            ,r.Ver
            ,ROW_NUMBER() OVER(PARTITION BY r.StoreType, r.PVendor, r.Country, r.PLocation, r.Store, r.BlockNo, r.PPath, r.Ver ORDER BY r.Ver DESC) AS rn
            FROM [T1].[dbo].PlanoGram r) q
   WHERE p.rn = 1
  ) p
ON a.storeType = p.StoreType AND
   a.vendorCode = p.PVendor AND
   a.countryCode = p.Country AND
   a.cityCode = p.PLocation AND
   a.storeCode = p.Store AND
   a.gridCodeX = p.BlockNo

Open in new window

ambidextrousCommented:
Hi Ken - i've corrected the aliases for those errors below.  (i added them in bold).  Typically the binding issues are due to incorrect aliasing. Maybe it will be nice to me this time...

---

SELECT
  a.countryCode
 ,a.cityCode
 ,a.storeCode
 ,a.storeType
 ,a.vendorCode
 ,a.rc
 ,a.gridCodeX
 ,a.gridCodeY
 ,a.blockDescription
 ,p.PPath
 ,p.Ver
FROM
  dbo.Block a
LEFT OUTER JOIN
 (
  SELECT q.StoreType
        ,q.PVendor
        ,q.Country
        ,q.PLocation
        ,q.Store
        ,q.BlockNo
        ,q.PPath
        ,q.Ver
    FROM (SELECT
             r.StoreType
            ,r.PVendor
            ,r.Country
            ,r.PLocation
            ,r.Store
            ,r.BlockNo
            ,r.PPath
            ,r.Ver
            ,ROW_NUMBER() OVER(PARTITION BY r.StoreType, r.PVendor, r.Country, r.PLocation, r.Store, r.BlockNo, r.PPath, r.Ver ORDER BY r.Ver DESC) AS rn
            FROM [T1].[dbo].PlanoGram r) q
   WHERE q.rn = 1
  ) p
ON a.storeType = p.StoreType AND
   a.vendorCode = p.PVendor AND
   a.countryCode = p.Country AND
   a.cityCode = p.PLocation AND
   a.storeCode = p.Store AND
   a.gridCodeX = p.BlockNo
SharathData EngineerCommented:
Did you try post http:#35242814? What's your comment?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.