Retrieving only the last row of a table, in the absence of a primary key

Question

I have a table that has 3 columns:

• Column 1: contains XML data (that contains a timestamp value)

• Column 2: contains the file name(with the path to it) corresponding to XML data record (as to which file that XML data is stored in)

• Column 3: contains the offset of the XML data within that file

For instance, different file names can be

1. C:\Extended Events\ReadWrite_0_130863592475800000
2. C:\Extended Events\ReadWrite_0_130863685030030000
3. C:\Extended Events\ReadWrite_0_130864300903760000

That is, the names assigned to the files are in incremental order.

Further each file name has different offset values in it, corresponding to the XML data value, returned above.

For instance,

C:\Extended Events\ReadWrite_0_130863592475800000 can have offset values 24576, 34816, 62976 (in an increasing order too)

Then, C:\Extended Events\ReadWrite_0_130864300903760000 can have offset values for itself independent of the above offset values.

I want to retrieve the file name and offset value corresponding to the last record returned i.e. if a total of 9 rows have been returned, I need the file name and offset value corresponding only to the 9th row. I also want it to be optimal.

Using the MAX function isn't helping my case. I tried max(file_name) and it simply opened the Documents folder.

Answer 1

If you want the row with the highest offset value for each file, then:

;WITH x AS (SELECT [Column 1], [Column 2], [Column 3],
  rn = ROW_NUMBER() OVER (PARTITION BY [Column 2] ORDER BY [Column 3] DESC
  FROM dbo.source_table
)
SELECT [Column 1], [Column 2], [Column 3]
  FROM x
  WHERE rn = 1
  ORDER BY [Column 2];

If yu just want the row with the highest offset across all files, you don't want MAX(), but you can use TOP (1):

SELECT TOP (1) [Column 1], [Column 2], [Column 3]
  FROM dbo.source_table
  ORDER BY [Column 3] DESC;