if in sql 1

[ralphonzo]

this is a bit of a wierd one.

i need to order a recordset by integer value descending, but if the value is above 999, then 1000 must be subtracted prior to the order. i'm doing this at the moment by looping through the recordset, dropping the values into an array and doing any necessary subtraction along the way. this is OK, but a little longwinded if there's a way of dragging the info out correctly from the db.

does anyone know if it's possible? i've played around with 'if' and 'case' but didn't get it right. the sql must work with access, mysql and mssql.

 

[gmmastros]

Do you mean that the same query must work for each of those 3 database engines? That's gonna be tricky to accomplish.

Access doesn't allow CASE statements. SQL Server doesn't allow IIF. And I have no idea about MySQL.

I suppose you could return the record unordered, then perform a MOD operation on the column, updating the recordset object (and not the database, so use a disconnected recordset), and then use ADO to sort the data.

MOD essentially returns the remainder of a division.

Ex:

250 MOD 1000 = 250
1250 MOD 1000 = 250
2250 MOD 1000 = 250

I suspect you would get better performance doing this in the database, but I suspect you'll need to do this in ASP using a recordset object.

BTW, to use MOD in SQL Server, use the % operator.

[tt][blue]Select 2250 % 1000[/blue][/tt]


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ralphonzo]

Ta George. I didn't know modulus was available for SQL. I've played around with them and will be able to use the allowed version with each db engine.