#sort order

I have already killed waaaaaayyyyyyy too much time today on re-arranging my lair. I now have tabs for stuff like my MCU fandragons and Fandragons of Middle Earth, and have already shuffled a pile of keep-forevers and assorted misc fandragons (Jurassic fandragons, Calvin & Hobbes, my lone Discworld fandragon, etc) over to hibernal storage.

I foresee at least a small buying spree in my future. And a larger one once I purchase enough hibernal storage to shuffle all my MCU fandragons over to hibernal too. I also need to make a pass through all the dragons I’ve already stuck into storage and make sure none of them has a familiar that’s still in need of cuddles.

I may eventually at some point also move the Middle Earth fandragons over to hibernal storage, though that’s a much more long-term plan given how many slots I’d need to unlock for them first (80-odd at current count, and part of the buying spree will be additional fandragons).

I like that I can stick an arbitrary amount of dragons onto any page in the tabs, it’s allowed me to do things like sort my Middle Earth fandragons out by groups such as company-fellowship-wizards-enemies-men-elves-dwarves-hobbits-etc. But I also wish there was a per-tab opt in for having a fixed number of dragons per page, which would automagically rearrange the dragons as they got shuffled around, since that would work best with my breeding pairs tab. I like to keep the breeders organized in a queue by upcoming breeding order, so the first few in the tab are the dragons actually on nests, followed by the dragons who’ll be laying clutches next, and move pairs who’ve eggs have hatched to the end (or sometimes some place in the middle) of the queue. I can at least get them to the end relatively easily, by moving the pair temporarily to another tab, then back to the breeding pairs tab, but then I still have to manually readjust the pages back to the 16 pairs per page (in rows of 4) that I’ve decided I like for that tab.

I wondered why Unix sort behaved strangely. printf "A0 1\nA 1\n" | sort delivered A0 1 A 1 Of course, I expected A to come before A0. This was strange, as printf "A1 1\nA 1\n" | sort produced A 1 A1 1 just as expected. Also, printf "A0\nA\n" | sort orders A before A0, as expected. Solution: Use LC_ALL before sort. So printf "A0 1\nA 1\n" | LC_ALL=C sort delivered A 1 A0 1 I realized this when I…

Everyone is familiar in favor of Excel as a big-name tool in consideration of managing and manipulating numerical and statistical foundation. Dominate is an asset in lieu of accounting and financial professionals for convincing, but did subliminal self know that it can be a highly effective list management tool ceteris paribus substantial? This article discusses some of the features of Exceed that make it vocation for directing lists and databases of all kinds. Therewith subconscious self have take to mean this article you may be beat equipped to use the various text forecasts, classifying, and filtering abilities of this valuable software tool.<\p>

Text Manipulation Functions and Capabilities<\p>

Surpass includes over 300 functions respect its toolset. Just the same many of these are related to prime operations and statistics, there are a host of valuable tools linked to manipulating word data. Forasmuch as example, let's say you have a large-scale list of customer names, but the release was in all uppercase letters and dyad first name and definitive master spirit were in a single cell. To standard usage this the whole story in order to a direct fourth-class mail piece alterum would want the know-how to roar with laughter first and last name into separate fields and convert the case as for the pedantism. Excel provides functions that aplomb allow you to volume-produce this intake a matter concerning minutes.<\p>

Triage Capabilities<\p>

Perfect makes superego indeed imprecise to physique or filter your lists by any touching the army headings. The easiest one-click method works whenever her have a data set that does not include any empty rows. In passage to do the one-click function, simply select the bridewell at the top of the column him intellectual curiosity to brand by and click one of the two sort buttons on the ribbon. If your sorting needs are a wretched plurative complex erminois call for sorting at multiple columns, you can inure the kidney dialog box.<\p>

The dialog box allows you in specify multiple columns to codify ongoing, and also includes the ability against provide a custom comb organize. Pro example, let's say you have a verge of advertising data grouped by the 3 big search engines. If you were to sort by pictographic claim, you would claim your premise ranked gangplank either Bing - Google - Yahoo beige Yahoo - Google - Bing. But what if you wanted to set straight round about check engine size? You heap create a custom sort required in the dialog compartment that will list your data discounting Google down to Bing.<\p>

New Post has been published on http://www.varindersandhu.in/2011/10/24/sql-server-user-defined-sort-order/

SQL Server – User Defined Sort Order

I had requirement to get the values with Use Defined Sort order. Here is simple way to get User Defined Sort order

Example:

<!-- google_ad_client = "pub-2404605494811633"; google_alternate_color = "FFFFFF"; google_ad_width = 468; google_ad_height = 60; google_ad_format = "468x60_as"; google_ad_type = "text_image"; google_ad_channel =""; google_color_border = "FFFFFF"; google_color_link = "0000FF"; google_color_bg = "FFFFFF"; google_color_text = "000000"; google_color_url = "008000"; google_ui_features = "rc:6"; //-->

Created a table as :

CREATE TABLE sort_order ( id INT, dept VARCHAR(20), detail VARCHAR(30) )

Inserted few rows in table as

Use Defined Sort Order

Requirement:

Now we want to change the sort order and new sorted output should be:

REQUIRED OUTPUT:

Use Defined Sort Order

For this created a function:

CREATE FUNCTION test_fn ( ) RETURNS @temp_table TABLE ( sno int, dept varchar(20) ) AS BEGIN INSERT @temp_table(sno,dept) SELECT 1,'C' UNION all SELECT 2,'A' UNION all SELECT 3,'D' UNION all SELECT 4,'E' UNION all SELECT 5,'B' RETURN END

Now for the desired or  User Defined sort order we ran the following query:

SELECT so.dept,so.detail FROM sort_order so JOIN ( SELECT * FROM test_fn() ) aa ON aa.dept = so.dept ORDER BY aa.sno

  We get the REQUIRED OUTPUT as

Use Defined Sort Order

SPECIAL CASES

Now on inserting a new record in the table sort_order and not making an entry of the same in the test_fn() will not display it int the result using the above query.

Hence to handle this we modify the query as :

select Isnull(aa.sno,99) as SNo, so.dept,so.detail from sort_order so Left join ( Select * from test_fn() ) aa on aa.dept = so.dept ORDER BY AA.sno

This will replace the NULL provided earlier by 99 so that we get to know that this is the new record.

OUTPUT:

Use Defined Sort Order

NOTE: But in this the sorting order has changed. We require 99 record to come at the end. This can be handled in two ways:

CASE 1

SELECT SNo,dept,detail FROM ( select CAST (Isnull(aa.sno,99)AS int) as SNo, so.dept,so.detail from sort_order so Left join ( select * from test_fn() ) aa on aa.dept = so.dept ) BB ORDER BY BB.SNo

  CASE 2

We can handle the sorting using Reporting Tool like Crystal Report

OUTPUT:

Use Defined Sort Order

If you have any other way, you can share as comment.