SQLTeam.com | Weblogs | Forums

Order BY - Decimal HELP!

tsql
sql2012
sql2014
sql2008

#1

select sum(woitem.qtytarget) as total, wo.num, sysuser.username
from woitem
join wo
ON wo.id = woitem.woid
Join moitem
on moitem.id = woitem.moitemid
Join mo
ON mo.id = moitem.moid
LEFT JOIN SYSUSER ON mo.userid = sysuser.id
group by sysuser.username, num
order by wo.num Asc

wo.num starts with 1000:001, 1001:001, 1002:001......999:001, 998:001, 99:001

But I need an order with displays 1:001, 2:001, 3:001,.....999:001, 1000:001, 1001:001, 1002:001


#2
ORDER BY 
	CAST(STUFF(wo.num+':',CHARINDEX(':',wo.num+':'),LEN(wo.num), '') AS INT);

#3

Hello James. it throws an exceptiom. Function unknown STUFF


#4

Your SQL does not look like SQL Server.


#5

Its a firebird sql. But it works like normal sql. Is there any way I can limit the number upto N decimals? New to coding so.. In result set as you could see 3 columns, total, wo.num and username. Both total and wo.num has to be limited to N decimals.


#6

Try LEFT function instead of STUFF. What are the string functions available to you? Is CHARINDEX and CAST available? You might get better answers at a forum that specializes in firebird.

CAST(LEFT(wo.num+':',CHARINDEX(':',wo.num+':')-1) AS INT);

#7

:slight_smile: More flavours of SQL than I have had hot dinners ...

You'd probably be best off on a Firebird forum as folk there will know the specifics. This is a Microsoft SQL Server forum. Don't want to drive you away ... but don't want to waste anyone's time barking up the wrong tree either :smile:


#8

lol. So true.