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: 
Another option without `UNION`, you could use `TOP 1 WITH TIES` and `ROW_NUMBER() OVER()` like this

```
SELECT TOP 1 WITH TIES * 
From YourTable 
WHERE ItemCode like '987%' OR ItemCode like '123%'
ORDER BY ROW_NUMBER() OVER(PARTITION BY LEFT(ItemCode,3) ORDER BY Itemcode DESC) 

```
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

```