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
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.
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);
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