CODEWITHSUNDEEP
sqlsql-servert-sql
manish patat
manish patat

Reputation: 19

Maximum Number string datatype in sql

My table is here

enter image description here

My SQL query

SELECT isnull(Max(CardNo),'0-0') FROM PRCCoatingData Where PRCNO='N001AHH'

But output was

N001AHH-9

I have get max no 10 but only 9 showing. How it possible?

Thanks in advance.

Upvotes: 0

Views: 70

Answers (2)

Alan Burstein
Alan Burstein

Reputation: 7928

Another way to handle this. First the sample data:

create table proccoatingdata (
    prcno  VARCHAR(255),
    Cardno VARCHAR(255));
GO

INSERT     dbo.proccoatingdata
SELECT     f.X, CONCAT(f.X,'-',n.N)
FROM       (VALUES ('ABC123'),('XXXYYYZZZ')) AS f(X)
CROSS JOIN (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS n(N)
GO

SELECT * FROM dbo.proccoatingdata;

This gives us:

prcno           Cardno
--------------- -----------------
ABC123          ABC123-1
ABC123          ABC123-2
...
ABC123          ABC123-9
ABC123          ABC123-10
XXXYYYZZZ       XXXYYYZZZ-1
XXXYYYZZZ       XXXYYYZZZ-2
...
XXXYYYZZZ       XXXYYYZZZ-9
XXXYYYZZZ       XXXYYYZZZ-10

Note that I included a couple different prcno values in case that is what you are dealing with. The solution would be:

SELECT      TOP(1) WITH TIES p.prcno, p.Cardno
FROM        dbo.proccoatingdata AS p
CROSS APPLY (VALUES(CAST(SUBSTRING(p.Cardno,
             CHARINDEX('-',p.Cardno)+1,200) AS INT))) AS f(SortKey)
ORDER BY    f.SortKey DESC;

This returns:

prcno        Cardno
------------ ------------------
XXXYYYZZZ    XXXYYYZZZ-10
ABC123       ABC123-10

For better performance you can also create in indexed view. This provides the same benefits of the computed column but without the need to make changes to your existing table.

CREATE OR ALTER VIEW dbo.idxView WITH SCHEMABINDING AS
SELECT      p.Cardno, p.prcno, SortKey = CAST(SUBSTRING(p.Cardno,
                                           CHARINDEX('-',p.Cardno)+1,200) AS INT)
FROM        dbo.proccoatingdata AS p;
GO

CREATE UNIQUE CLUSTERED INDEX idxVw ON dbo.idxView(sortkey,cardno);
GO

You can then query the indexed view like this:

SELECT TOP(1) WITH TIES v.*
FROM     dbo.idxView AS v
ORDER BY v.SortKey DESC;

Again, correct answer but this time with a sort free execution plan.

BOOM. enter image description here

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271023

Use a computed column:

create table proccoatingdata (
    prcno varchar(255),
    _cardno int,
    prcnocardno as (concat(prcno, _cardno))
);

Then you can simply use _cardno for ordering:

SELECT TOP (1) CardNo
FROM PRCCoatingData 
WHERE PRCNO = 'N001AHH'
ORDER BY _CardNo DESC;

Upvotes: 1

Related Questions