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

```
Thanks to @jarlh, I used `UNION` to achieve what I need.

If anyone has a more general method which may easier to be extended to more criteria, please post an answer and I will accept it. Cheers.

```sql
SELECT * FROM(
    SELECT TOP 1 ItemCode FROM Table
    WHERE ItemCode LIKE '987%'
    ORDER BY 1 DESC
) AS A
UNION
SELECT * FROM(
    SELECT TOP 1 ItemCode FROM Table
    WHERE ItemCode LIKE '123%'
    ORDER BY 1 DESC
) AS B

```