Actuate Programming Journal

Report design from a guy who thinks he knows what he’s doing; you decide . . . .

Reordering… sorta

leave a comment »

Stop reading when you find this lame. I love figuring out little tidbits of SQL. Not so much the SQL, but figuring out how to use any language to elegantly do my work for me.

I have a report that uses data from a table to build out the layout of the report. The customer wished for me to re-order the results of the report in descending order and for reasons I won’t get into I couldn’t just slap a modifying desc into the report’s SQL statement. I had to do a one time fix of the data itself.

Now, I could have just piped out the existing data into a spreadsheet, changed the numbers used for ordering, concocted a bunch of update statements, and run those all back to back, but where’s the grace in that?

So, I hit up a buddy that is good with numbers. There should be a way to do this with a single update statement I said. Here it is:
update table set ordering_value = abs(ordering_value - 200);

Where 200 was my personal interpretation of max(ordering_value + 1). No need to code up a sub-select for that bit when I can easily figure out that value.

Before
A 1
B 2
C 3
D 4
E 5
...
Z 199

After
Z 1
...
E 195
D 196
C 197
B 198
A 199

Advertisements

Written by Douglas

20061018 at 18:43 pm

Posted in SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: