0

I have problem with sorting in SQL.

Data:

ID   accountCode
1     A99
2     A3792
3     A230
4     A2
5     AA2
6     AB23
7     EXMPLECODE

Query:

select top 1 accountCode
from AccountCodes
where accountCode like 'A%'
order by accountCode desc

Result:

A99

Expected Result:

A379

How can I get A379 result in this stuation?

Thank you for help

FY STUDIO
  • 1
  • 2
  • 2
    Remove the A, cast as integer. – jarlh Sep 16 '22 at 20:18
  • 2
    The sort you are getting in your `RESULT:` is called "Lexicographical" which is how a dictionary would sort, and it's how an alpha or alpha numeric string is sorted by pretty much any sorting algorithm. "A99" would be, in the dictionary, after "A379". Instead you want to sort these numerically, which means you need to turn them into a number (cut off the A, cast to a number, and sort). – JNevill Sep 16 '22 at 20:19
  • Does this answer your question? [Natural (human alpha-numeric) sort in Microsoft SQL 2005](https://stackoverflow.com/questions/34509/natural-human-alpha-numeric-sort-in-microsoft-sql-2005) – Heinzi Sep 16 '22 at 20:28
  • If your format is guaranteed to *always* be "single letter + number", a simple solution would be `ORDER BY LEN(accountCode) DESC, accountCode DESC`. – Heinzi Sep 16 '22 at 20:29
  • I see you have several answers to the problem. This type of question often actually is an indication that normalization has been violated. It almost always means that the column is storing two pieces of information. This violates 1NF. – Sean Lange Sep 16 '22 at 22:03

5 Answers5

0

substring into number, cast, then sort

select top 1 accountCode
from AccountCodes
where accountCode like 'A%'
order by CAST(SUBSTRING(accountCode, 2) AS INT) desc
IT goldman
  • 14,885
  • 2
  • 14
  • 28
0

You first need to remove the all non numeric characters and then cast it as integer

CREATE TABLE AccountCodes
    ([ID] int, [accountCode] varchar(4))
;
    
INSERT INTO AccountCodes
    ([ID], [accountCode])
VALUES
    (1, 'A99'),
    (2, 'A379'),
    (3, 'A230')
;

3 rows affected

select top 1 accountCode
from AccountCodes
where accountCode like 'A%'
order by CAST(stuff(accountCode, 1, patindex('%[0-9]%', accountCode)-1, '') as INT) desc
accountCode
A379

fiddle

Dale K
  • 25,246
  • 15
  • 42
  • 71
nbk
  • 45,398
  • 8
  • 30
  • 47
  • This solution it work thanks. but ı forgot to write sometimes data is change for example A2323 A23 A21333 how can ı optimize query for this situation ? – FY STUDIO Sep 16 '22 at 23:29
  • you changed your data, which is not allowed here, as it is a new question, and the proper way is to accept this one and ask a new question, still when you change your data you have to say which is the wanted result – nbk Sep 16 '22 at 23:35
  • I am sorry about the changed data. I am new here. I want to the same result A379 – FY STUDIO Sep 17 '22 at 18:22
  • And what is the logic 379 is not the highest number, nor is is in any way sortable, you need always an algorithm that can be programmed – nbk Sep 17 '22 at 18:30
0

Suppose #AccountCodes is your real table,

CREATE TABLE #AccountCodes
    ( [accountCode] varchar(30))
;
    
INSERT INTO #AccountCodes
    ([accountCode])
VALUES
    ( 'A99'),
    ( 'A379'),
    ( 'A230'),
    ('A2323'),
    ('A23'),
    ('A21333'),
    ('AB23'),
    ('EXMPLECODE')
;

Now create one #temp table

CREATE TABLE #temp  (ID int identity(1,1) primary key, [accountCode] varchar(30))

insert into #temp([accountCode])
select  accountcode from
(
select *
,cast(substring(accountcode,(PATINDEX('%[0-9]%',accountcode)),len(accountcode)) as int)newCode
from #AccountCodes
where PATINDEX('%[0-9]%',accountcode)>0
)t4
order by newCode

insert into #temp([accountCode])
select [accountCode]
from #AccountCodes
where PATINDEX('%[0-9]%',accountcode)<=0
order by [accountCode]


select * from #temp order by id

drop table #AccountCodes,#temp

You can throw sample data where it not working

KumarHarsh
  • 5,046
  • 1
  • 18
  • 22
0

we're extracting all the numbers from accountCode, ordering by the length of the number and then by the digits.

select   top 1 accountCode
from     t
where    accountCode like 'A%'
order by len(substring(accountCode, PatIndex('%[0-9]%', accountCode), len(accountCode))) desc, substring(accountCode, PatIndex('%[0-9]%', accountCode), len(accountCode)) desc
accountCode
A379

Fiddle

DannySlor
  • 4,574
  • 1
  • 3
  • 11
-2

In your query you're selecting the top 1 result where the accountCode is like "A%" which is obviously going to pick the first result: A99. You either have to change it to "A___%" or substring and sort.

EmilioW
  • 26
  • 3