title: SQL Logic: Need the row with the greatest date
body: <p>OK, I have the following query that builds a table of a set of customers who used a product off of their account during a period. The report than uses that table to see which customers have not used a product off of their account in the second time period.</p>
  
  <p>My issue is that for people who have used a product off of their account may have done so multiple times in that period. Therefore, when I pull back the data from the report for members who used a product in period one and not in period two, I am getting multiple rows because they used products multiple times.</p>
  
  <p>I only need the latest/greatest date of usage row and not the other rows. I tried using MAX(SSUSED_1.usedate) but I am noting getting what I want. Any help would be appreciated!</p>
  
  <pre><code>WITH cte AS (
      SELECT
          SS.memid
      FROM SS
      INNER JOIN SSUSED ON SS.ssid = SSUSED.ssid
      INNER JOIN MEMBERS AS MEMBERS_1 ON SS.memid = MEMBERS_1.memid
      INNER JOIN PRODUCTS ON SS.productid = PRODUCTS.productid
      INNER JOIN PRODUCTCATS ON PRODUCTS.productcatid = PRODUCTCATS.productcatid
      INNER JOIN EMPLOYEES ON SSUSED.employeeid = EMPLOYEES.employeeid
      WHERE
          MEMBERS_1.siteid = @rvSite
          AND SSUSED.usedate BETWEEN @rvStartWeek2 AND @rvEndWeek2
          AND PRODUCTS.productcatid IN (27, 28, 29, 58, 77, 75, 30, 61, 31, 32, 47))
  
  SELECT DISTINCT 
      SS_1.memid,
      MEMBERS_1.scancode,
      MEMBERS_1.lname,
      MEMBERS_1.fname,
      PRODUCTS_1.description,
      EMPLOYEES_1.lname AS TrainLName,
      EMPLOYEES_1.fname AS TrainFName,
      SSUSED_1.usedate,
      SSUSED_1.transactiontype
  FROM SS AS SS_1
  INNER JOIN SSUSED AS SSUSED_1 ON SS_1.ssid = SSUSED_1.ssid
  INNER JOIN MEMBERS AS MEMBERS_1 ON SS_1.memid = MEMBERS_1.memid
  INNER JOIN PRODUCTS AS PRODUCTS_1 ON SS_1.productid = PRODUCTS_1.productid
  INNER JOIN PRODUCTCATS AS PRODUCTCATS_1 ON PRODUCTS_1.productcatid = PRODUCTCATS_1.productcatid
  INNER JOIN EMPLOYEES AS EMPLOYEES_1 ON SSUSED_1.employeeid = EMPLOYEES_1.employeeid
  WHERE
      MEMBERS_1.siteid = @rvSite
      AND SSUSED_1.usedate BETWEEN @rvStartWeek1 AND @rvEndWeek1
      AND PRODUCTS_1.productcatid IN (27, 28, 29, 58, 77, 75, 30, 61, 31, 32, 47)
      AND NOT EXISTS
          (SELECT
              1 AS Expr1
          FROM cte AS cte_1
          WHERE SS_1.memid = memid)
  ORDER BY TrainLName, MEMBERS_1.scancode, SSUSED_1.usedate DESC
  </code></pre>