0

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.

  • So if there are several rows for some file, then the same XML and file name are repeated for each distinct offset, correct? And you want to sort the rows by the file name column, then the offset column and take the last row from the results set? – Andriy M Sep 14 '15 at 13:03
  • The file names might be repeated. The XML data is unique, however the offset might be repeated at times. And yes, logically the last row, after having the table sorted (in an ascending order) according to the file names and then according to the offset values would contain what is desired. – Ritesh Bhakre Sep 14 '15 at 13:45
  • 2
    Confused. How does a query "open the documents folder"? Also I'm not sure I follow, do you want the "last row" (the one with the largest offset value) from each file, or just the row with the highest offset across all files? Tabular sample data and desired results go a lot further than a word problem. – Aaron Bertrand Sep 14 '15 at 14:41
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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.