Tag Archives: Data

WordPress MU: Delete Empty Posts

Sometimes I bring information to a couple of my other WordPress blogs via RSS feed. It’s a nice feature, allowing you to create several posts at once without manual entry. Unfortunately if RSS feed is broken or improperly formatted it can result in blank posts imported into the blog.

I was looking for a WordPress plugin that would allow me to mass-delete empty posts, but apparently none exist. You can delete posts based on date, tags, category, but not the content. Fortunately if you have access to phpMyAdmin of your MySQL installation – there is a solution. Continue reading →

Method ‘System.Object CompareObjectEqual(System.Object, System.Object, Boolean)’ has no supported translation to SQL. Solution to error

I was running a basic LINQ 2 SQL statement:

From role In db.user_role _
Where role.USER_ID = Session("user_id") Select role

when I encountered following error message:

Method ‘System.Object CompareObjectEqual(System.Object, System.Object, Boolean)’ has no supported translation to SQL.

After a little research I found the solution.
Continue reading →

Select specific groups using DENSE_RANK

Imagine after running a query like this:

SELECT ContactName, Country FROM Customers ORDER BY Country

on Northwind database and getting following result:

ContactName           Country
Patricio Simpson      Argentina
Yvonne Moncada        Argentina
Sergio Gutiérrez      Argentina
Georg Pipps           Austria
Roland Mendel         Austria
Catherine Dewey       Belgium
Pascale Cartrain      Belgium
Anabela Domingues     Brazil
Paula Parente         Brazil
Bernardo Batista      Brazil
Lúcia Carvalho        Brazil
Janete Limeira        Brazil
Aria Cruz             Brazil
André Fonseca         Brazil
Mario Pontes          Brazil
Pedro Afonso          Brazil
Elizabeth Lincoln     Canada
Jean Fresnière        Canada
Yoshi Tannamuri       Canada
...

You’re asked to retreive only the 2nd and the 5th group of contacts. How do you do it? Continue reading →

T-SQL String Aggregate in SQL Server

T-SQL dialect of SQL doesn’t have aggregate functions for strings, but there is an easy workaround using magic of XML.

Consider Employees table of the Northwind database. When I run following query:

SELECT Country, FirstName FROM Employees ORDER BY Country, FirstName

I get following result:

Country FirstName
UK      Anne
UK      Michael
UK      Robert
UK      Steven
USA     Andrew
USA     Janet
USA     Laura
USA     Margaret
USA     Nancy

Now I want to combine first names into comma separated strings grouped by country. Continue reading →

Group By and Aggregates in .NET DataTable

Often there is a need to perform group by operations on in-memory .NET data tables. You can’t run a standard SQL statement and LINQ is not available prior version 3.0 of .NET. To compensate for this shortcoming I put together a small function that works in .NET 2.0. As input parameters it accepts source DataTable, column name to group by and a column name to perform aggregate operation on. It returns a grouped data as data table:

Function GroupBy(ByVal i_sGroupByColumn As String, ByVal i_sAggregateColumn As String, ByVal i_dSourceTable As DataTable) As DataTable


        Dim dv As New DataView(i_dSourceTable)

        'getting distinct values for group column
        Dim dtGroup As DataTable = dv.ToTable(True, New String() {i_sGroupByColumn}) 

        'adding column for the row count
        dtGroup.Columns.Add("Count", GetType(Integer)) 

        'looping thru distinct values for the group, counting
        For Each dr As DataRow In dtGroup.Rows
            dr("Count") = i_dSourceTable.Compute("Count(" & i_sAggregateColumn & ")", i_sGroupByColumn & " = '" & dr(i_sGroupByColumn) & "'")
        Next        

        'returning grouped/counted result
        Return dtGroup
End Function

The function first gets distinct values for group-by column, by creating a data view from source data table and using DataView’s “ToTable” method. It then loops thru these distinct values performing aggregate function on the source table using DataTable’s “Compute” method – Count in this case, but it can easily be replaced with other aggregates.