Question: I have a list of ID’s Shown below.

```
AT0920130004
AT0920130005
AT0920130006
AT0920130007
AT0920130008
AT0920130009
AT0920130010
AT1020130001
AT1020130002
AT1020130003
AT1020130004
AT1020130005
AT1120130003
AT1120130004
AT1120130005
AT1120130006

```

Here an example record has the format `ATmmyyyyxxxx`.

where 

`AT` represents `location`,

`mm` represents `month` eg 10 would be `October`

`yyyy` represents `year` eg. `2013`

`xxxx` represents the `increasing seed of numbers`.

Now I need to select an ID which is generated in the `end of the month`.
For ex: last id of September i.e. AT0920130010.
Any suggestions are appreciated.

Answer: 
You can use this query to get max ID for each month and year

```
select substring([id], 3, 6) MonthYear, max([id]) MaxID
from yourtable
group by substring([id], 3, 6)

```

---

To get max ID for one specific month, you can use this query:

```
select max([id])
from yourtable
where cast(substring([id], 5, 4) as int) = 2013 -- year
    and cast(substring([id], 3, 2) as int) = 9 -- month

```
try this(this will return whole field, and just id):

```
SELECT YOUR_FIELD, MAX(RIGHT(YOUR_FIELD,4)) AS just_id FROM YOUR_TABLE WHERE RIGHT(YOUR_FIELD,LENGTH(YOUR_FIELD)-2) LIKE '09%'

```