MySQL: Sort By Column Values

Tuesday, December 13, 2011

In MySQL, sorting the data using columns can easily be done by using the ORDER BY clause. Recently, I came across a requirement where I had to sort the data according to the specific values of a column. For example, if a column has the numbers written as words (one, two, three etc.), the values need to be sorted according to their numerical equivalent rather than the default alphabetical sorting.

By default, the ORDER BY clause will sort data using alphanumeric sorting. But there is a trick by using the MySQL field(str,str1,str2,str3...) function to give a different order to the values in the columns. The function finds the position of str in the list of strings str1, str2, str 3...

Here is an example on how you can combine the field function with the ORDER BY clause:

ORDER BY field(`column_name`, 'One', 'Two', 'Three', 'Four', 'Five')

The above code will sort the data according to the column values One, Two, Three, Four, Five.

MySQL: Exporting Selective Columns to CSV

Friday, November 11, 2011

There are many to export large data sets from MySQL in CSV. If you need data for the complete database or a table in the database, the 'mysqldump' command is a great solution. But sometimes you might need to just export a subset of columns in the database, filtered by where clause. This can be achieved by using the SELECT ... INTO clause. It lets you specify the columns you need for your CSV and you can easily use aggregate functions (SUM, AVG etc.), WHERE clause, GROUP BY clause, ORDER BY clause etc. to get the appropriate data to be exported. Also, custom CSV delimiters can be specified to be used for the exported data. You would need to have filesystem access to the MySQL server to run this command.

Here is an example query:

SELECT `column1`, `column2`, `column3` INTO OUTFILE 'c:\\data\\today.csv'

    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

    LINES TERMINATED BY '\n'

FROM `tablename`

For more details and options please refer to the MySQL reference for the command.

Another way to export data from MySQL is by using MySQL Workbench, which is a free tool to administer MySQL databases. Though, the above command is really helpful if you want to schedule export jobs.

C# Default Arguments [Optional Arguments]

Monday, September 05, 2011

Coming from the C++ world, one of the things I missed in C# was the support for Default Arguments. Default arguments are used to create functions which could have a few optional arguments with default values. Thus, you could make overloaded calls to the method without creating different function prototypes if the purpose could by using a default value of one of the parameters. Many other common languages like PHP, VB.NET, JavaScript etc. also support this feature. It was surprising that C# did not support it.

With C# 4.0 (2010), the support for this feature has been added to the language. The new Optional Arguments in C# 4.0 allows you to specify the default values of parameters in case there values are not provided from the function call. Here's a small example to illustrate the basics of Optional Arguments in C#:

//C#

// Function with optional arguments
void OptionalArgExample(int reqdField, string opt = "default value")
{
    // do something
}

// Calling the method with just the required argument
OptionalArgExample(10);

// Calling the method with both the arguments
OptionalArgExample(10, "some text");

For more information, read the MSDN article on Named and Optional Arguments in C#