SQL - Returning CTE with Top 1

Question

I am trying to return a set of results and decided to try my luck with CTE, the first table "Vendor", has a list of references, the second table "TVView", has ticket numbers that were created using a reference from the "Vendor" table. There may be one or more tickets using the same ticket number depending on the state of that ticket and I am wanting to return the last entry for each ticket found in "TVView" that matches a selected reference from "Vendor". Also, the "TVView" table has a seed field that is incremented.

I got this to return the right amount of entries (meaning not showing the duplicate tickets but only once) but I cannot figure out how to add an additional layer to go back through and select the last entry for that ticket and return some other fields. I can figure out how to sum which is actually easy, but I really need the Top 1 of each ticket entry in "TVView" regardless if its a duplicate or not while returning all references from "Vendor". Would be nice if SQL supported "Last"

How do you do that?

Here is what I have done so far:

with cteTickets as (
    Select s.Mth2, c.Ticket, c.PyRt from Vendor s 
    Inner join 
    TVView c on c.Mth1 = s.Mth1 and c.Vendor = s.Vendor
)
Select Mth2, Ticket, PayRt from cteTickets
Where cteTickets.Vendor >='20'
  and cteTickets.Vendor <='40'
  and cteTickets.Mth2    ='8/15/2014'
Group by cteTickets.Ticket
order by cteTickets.Ticket

Answer 1

Several rdbms's that support Common Table Expressions (CTE) that I am aware of also support analytic functions, including the very useful ROW_NUMBER(), so the following should work in Oracle, TSQL (MSSQL/Sybase), DB2, PostgreSQL.

In the suggestions the intention is to return just the most recent entry for each ticket found in TVView. This is done by using ROW_NUMBER() which is PARTITIONED BY Ticket that instructs row_number to recommence numbering for each change of the Ticket value. The subsequent ORDER BY Mth1 DESC is used to determine which record within each partition is assigned 1, here it will be the most recent date.

The output of row_number() needs to be referenced by a column alias, so using it in a CTE or derived table permits selection of just the most recent records by RN = 1 which you will see used in both options below:

-- using a CTE

WITH
      TVLatest
      AS (
            SELECT
                  * -- specify the fields
                , ROW_NUMBER() OVER (PARTITION BY Ticket
                                     ORDER BY Mth1 DESC) AS RN
            FROM TVView
            )
SELECT
      Mth2
    , Ticket
    , PayRt
FROM Vendor v
      INNER JOIN TVLatest l  ON v.Mth1 = l.Mth1
                            AND v.Vendor = l.Vendor
                            AND l.RN = 1
WHERE v.Vendor >= '20'
      AND v <= '40'
      AND v.Mth2 = '2014-08-15'
ORDER BY
      v.Ticket
;

-- using a derived table instead

SELECT
      Mth2
    , Ticket
    , PayRt
FROM Vendor v
      INNER JOIN (
                  SELECT
                        * -- specify the fields
                      , ROW_NUMBER() OVER (PARTITION BY Ticket
                                           ORDER BY Mth1 DESC) AS RN
                  FROM TVView
                 ) TVLatest l  ON v.Mth1 = l.Mth1
                            AND v.Vendor = l.Vendor
                            AND l.RN = 1
WHERE v.Vendor >= '20'
      AND v <= '40'
      AND v.Mth2 = '2014-08-15'
ORDER BY
      v.Ticket
;

---

please note: "SELECT *" is a convenience or used as an abbreviation if full details are unknown. The queries above may not operate without correctly specifying the field list (eg. 'as is' they would fail in Oracle).