Return the Greater of The Fields Last Column in SQL-CodePudding

I am faced with the problem of returning the greater of the two values in the "entryId" field, that precedes the "|" mark. Each entryId field will have different numbers depending on the day, and I need to select the values that are the same, and have a higher number that precedes the "|" mark.

For example, I will need to select entryId =
'800181373149|3', '800181373112|3', '800181373186|3'

and not '800181373186|1'

Here is what my code is:

    SELECT 
     [entryId]
    ,[startDate]
    ,[endDate]
    FROM [data]
    WHERE entryId LIKE '8001813731%';

Here is the output:

entryId	startDate	endDate
800181373149\|3	2021-09-28T07:21:00-05:00	2021-09-28T16:03:00-05:00
800181373112\|3	2021-09-28T05:21:00-07:00	2021-09-28T12:00:00-07:00
800181373186\|3	2021-09-28T07:21:00-05:00	2021-09-28T14:00:00-05:00
800181373186\|1	2021-09-28T07:21:00-05:00	NULL

CodePudding user response：

Using a row_number partitioned by the part before the pipe.

SELECT 
  [entryId]
, [startDate]
, [endDate]
FROM
(
    SELECT 
      entryId
    , startDate
    , endDate
    , rn = ROW_NUMBER() OVER (PARTITION BY LEFT(entryId, PATINDEX('%_|%', entryId)) ORDER BY entryId)
    FROM [data]
    WHERE entryId LIKE '8001813731%'
) q
WHERE rn = 1;

CodePudding user response：

Not sure if you want a single outcome with the highest value or all the vallues with the highest startdate number. Anyway in eighter case you could use the AND operator to make a second argument to add the startdate value.

SELECT 
 [entryId]
,[startDate]
,[endDate]
FROM [data]
WHERE entryId LIKE '8001813731%' AND startDate > 1;