Arejae.Com

Can you guess which one is a max number if the data type for the field is not an integer ? perhaps it is varchar or nvarchar field.

10
1
2
3
14
15
9
8

The answer is 9. I believe it is because ascii value for 9 = 57 and the ascii value for 25 = 50

Then, how you want to get the max value using SQL command ?

Ok, before we get the answer, why not we play around with the data first.

Create sample data.

select ‘10′ as BoxNo INTO #temp
insert into #temp values (’1′)
insert into #temp values (’2′)
insert into #temp values (’3′)
insert into #temp values (’14′)
insert into #temp values (’15′)
insert into #temp values (’9′)
insert into #temp values (’8′)

Test

select max(BoxNo) from #temp

The answer will be 9

Solution

select max(cast(BoxNo as int)) from #temp

This is the answer that we want, 15 .

Now, how about to find the second max number ? I’m going to use same sample above but first, let convert the field to int. Well, IMHO the field should be int at the first place and not the char,varchar or nvarchar.

alter table #temp alter column BoxNo int

–Get second max number

select max(cast(BoxNo as int)) from #temp
where BoxNo not in (select max(cast(BoxNo as int)) from #temp)

–or

SELECT * FROM #temp t1 WHERE (2 = (SELECT COUNT(DISTINCT (t2.BoxNo))
FROM #temp t2 WHERE t2.BoxNo >= t1.BoxNo))

err..how about the third max number ? ok,here you go.

SELECT * FROM #temp t1 WHERE (3 = (SELECT COUNT(DISTINCT (t2.BoxNo))
FROM #temp t2 WHERE t2.BoxNo >= t1.BoxNo))

Can you see the patern ?

Ok, hopefully it helps. If it does, kindly leave a comment so I know it works for you.

If you're new here, you may want to subscribe to my RSS feed or get my latest post directly in your mailbox. Thanks for visiting !