Question: Say I have a table which has a column named `ItemCode`, it has fixed format xxx-xxxx where x is [0-9], for example a possible value of `ItemCode` is 097-1234

Now I would like to select the largest `ItemCode` which starts with `987` AND the last `ItemCode` starts with `123`, so I am trying to do something like (This is wrong)

```
SELECT TOP 1 ItemCode From Table 
WHERE ItemCode like '987%' OR ItemCode like '123%'
ORDER BY 1 DESC

```

So how can I write a SQL which can select the last `ItemCode` of each criteria? Is there any general method which can extend to select top `N` rows on `M` such criterias? 

(assuming there exists data fulfills the criteria, here 2 rows should be returned: largest `ItemCode` starts with `987` and largest `ItemCode` starts with `123`)

Answer: 
You can use `ROW_NUMBER()` in a CTE for a more generalised form:

```
;With Ordered as (
    select
        *,
        ROW_NUMBER() OVER (
            PARTITION BY SUBSTRING(ItemCode,1,3)
            ORDER BY ItemCode desc) as rn
    from
        Table
    where
        ItemCode like '987%' or
        ItemCode like '123%'
)
select *
from Ordered
where rn = 1

```

As I alluded to in the comments, if possible I'd change the structure so that the `ItemCode` elements are separately stored, which would make for a simpler internal query form that could also more easily benefit from indexes. E.g. something like:

```
;With Ordered as (
    select
        *,
        ROW_NUMBER() OVER (
            PARTITION BY ItemCode_Prefix
            ORDER BY ItemCode_Suffix desc) as rn
    from
        Table
    where
        ItemCode_Prefix in (987,123)
)
select *
from Ordered
where rn = 1

```
Complete solution based on ROW\_NUMBER() function:

```
use tempdb;
go

-- Test data
create table #test_data
(ItemCode char(8) not null);

insert into #test_data
values 
  ('097-1234'),
  ('097-1243'),
  ('097-7890'),
  ('012-1234'),
  ('912-1234'),
  ('123-1234'),  -- second max for '987,123'
  ('123-1234'),
  ('123-0001'),
  ('123-0932'),
  ('987-1234'),
  ('987-5643'),
  ('987-7890'),  -- first max for '987,123'
  ('000-7890');
go
-- Test data

-- Code
create proc dbo.top_n_from_m
  @criterias varchar(max)
as
  set nocount on;

  declare @crs table 
    (id int not null identity (1, 1) primary key,
     string char(3) not null);

  insert into @crs (string)
  select value
  from string_split(@criterias, ',')

  select t.ItemCode
  from 
    (select t.ItemCode,
            c.id, 
            row_id = row_number() over (partition by c.id order by t.ItemCode desc)
    from #test_data as t
      join @crs as c on t.ItemCode like c.string + '-%') as t
  where t.row_id = 1
  order by t.id
go
-- Code

-- Test
execute dbo.top_n_from_m @criterias = '987,123'

select ItemCode
from #test_data
order by ItemCode
-- Test

-- Clear
drop table #test_data;
drop proc dbo.top_n_from_m;
-- Clear

```