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.

Reset Identity Field in MS-SQL

Tuesday, April 15, 2008

There are a lot of occasions when you would need to reset an 'Identity' field in the database table. For doing this in MS-SQL, you just need to execute this statement:

DBCC CHECKIDENT ('', RESEED)

This statement will RESEED the identity field to current maximum value in the column. You may also give a custom reseed value to the column:

DBCC CHECKIDENT ('', RESEED, )

Specify an integer number in place of the new_value. For example, to reset the identity column in 'tblA' to the value 20, use:

DBCC CHECKIDENT ('tblA', RESEED, 20)