title: Partition by to get the correct date
body: <p>I would like to retrieve the oldest date under the <strong>FirstScanned</strong> Column. My issue is that a product can be registered again at some point as a new product, when this happens, I'd like the <strong>FirstScanned</strong> to be retrieve the oldest scanned date based on the newest scan on <strong>Location 1</strong></p>
  
  <p>In the following example I'm trying to retrieve the correct FirstScanned Date without success:</p>
  
  <pre><code>CREATE TABLE [dbo].[Products123](
  [ID] [int] NOT NULL,
  [GTIN] [varchar](50) NULL,
  [LocationID] [int] NULL,
  [UserID] [int] NULL,
  [Created] [datetime] )
  
  insert into Products123(ID, GTIN, LocationID, UserID, Created) 
  Values(1, '12345678910', 1, 3, '2017-06-30 14:58:07.693'), -- Location "1" is when products was registered/scanned for this first time
        (2, '12345678910', 5, 3, '2017-06-30 15:25:12.287'), -- The product is scanned into a new location
        (3, '12345678910', 17, 3, '2017-06-30 14:58:07.693'), -- The product is now scanned into the "end" location and is considered to be closed
        (4, '12345678910', 1, 7, '2017-08-01 11:34:16.347'), -- A month later the same productID has been registered,
        (5, '12345678910', 4, 7, '2017-08-01 11:36:16.460') -- etc
  
  
     DECLARE @Prev8workingdays date = CASE
          WHEN datepart(dw, getdate()) IN (2,3,4) THEN dateadd(day,-14, getdate()) 
          WHEN datepart(dw, getdate()) IN (1) THEN dateadd(day,-13, getdate()) 
          ELSE dateadd(day,-12, getdate())
     END
     DECLARE @Pre6WorkingDay date = CASE
          WHEN datepart(dw, getdate()) IN (2) THEN dateadd(day,-9, getdate()) 
          WHEN datepart(dw, getdate()) IN (1) THEN dateadd(day,-8, getdate()) 
          ELSE dateadd(day,-7, getdate())
     END
  
    select p.GTIN, p.FirstScanned as FirstScan, p.Created As lastScan
  from
  (
      select p.GTIN
          ,p.LocationID
          ,p.Created
          ,min(p.Created) over (partition by p.GTIN) as FirstScanned
          ,max(p.Created) over (partition by p.GTIN) as LastScanned
      from Products123 p
  ) p
  where p.LastScanned = p.Created and LocationID not in (15,16,17) AND  p.FirstScanned &lt; @Prev8workingdays
  Order by FirstScanned
  </code></pre>
  
  <p>My result looks like this:</p>
  
  <pre><code>   GTIN        |       FirstScanned      |   LastScanned       
     12345678910 | 2017-06-30 14:58:07.693 |2017-08-01 11:36:16.460
  </code></pre>
  
  <p>But it Should be:</p>
  
  <pre><code>   GTIN        |       FirstScanned      |   LastScanned       
     12345678910 | 2017-08-01 11:34:16.347 |2017-08-01 11:36:16.460
  </code></pre>