Looking for an easy way (or the "right way") to filter and search SQL Server database rows-CodePudding

I've got a smallish SQL Server (max size is less than 1GB) and I need to be able to run quite a few chained queries on the data.

My database looks a bit like this:

id | val1 | val2 | val3 | val4 | date_modified
1  | 'asd'| '1'  | 'd'  | 'u'  | 'xx/xx/xxxx'

The data I really need from this table is quite complicated. For each unique value of val4 I need to find the two most recent rows based on the date_modified row. With these two entries (well, 2 entries for each unique val4 value) I need to search all the columns in this for a regular expression, and then return the row(s) that match.

I've tried playing around with a stored procedure to do all of this, but it becomes quite complicated because I'm trying to chain the queries together in an algorithmic sort of way. I'm not sure if the way I'm going about it is wrong, or if it's easier to use some sort of java/spring abstraction layer to make this easier. I've considered just reading the entire DB into a java collection so I can apply an algorithm to it, but I don't know if that's a viable solution given how large this DB could become.

Sample data:

id  val1                                      val4  date_modified
1   \"test\" : \"123\", \"test2\" : \"555\"    'a'  now
2   \"test\" : \"123\", \"test2\" : \"555\"    'a'  2 hours ago
3   \"test\" : \"123\", \"test2\" : \"555\"    'a'  5 hours ago
4   \"test\" : \"123\", \"test2\" : \"555\"    'b'  10 hours ago
5   \"test123\" : \"123\", \"test2\" : \"555\"  c'  now

So if I search for "test" it would return:

1, 2, 4, 5 because:

1 & 2 contain "test" in their val1 columns, and they are the two most recent entries with a value4 of 'a'. 4 also contains "test" and is the most recent of val4 'b' and 5 for the same reason.

CodePudding user response：

You could use rank in a CTE and use rank() as follows to get the first 2 most recent values.
After that you can use LIKE in the outer query or use regex in Java as you find works best.

with cte as
(
  Select Id, val1,val2,val3,val4,
  date_modified,
  Rank() over(partition by val4 
      Order by date_modified desc) 
      as ranking
)
Select * from cte 
where ranking <=2
/* Add like condition here? */
Order by id