Category Archives: SQL

Full-Text Search with SQL Server 2008

The full-text search capabilities of SQL Server 2008 make it possible to search text across multiple columns, weight search terms, and search by proximity. A full-text query can be much faster than the equivalent LIKE query. The first step is … Continue reading

Posted in SQL, SQL Server | Leave a comment

Taking the top 5 of grouped ranked data in PostgreSQL

Working from a list of my ancestors’ occupations I wanted to list the top 5 occupations in each occupational category per decade, so that given year category description total … 1851 Agriculture Ag Lab 4 1851 Agriculture Farm Bailiff 1 … Continue reading

Posted in PostgreSQL, SQL | Leave a comment

Concatenating column from many side of one-to-many into single record using STUFF

SELECT OneTable.col1, OneTable.col2, STUFF ( ( SELECT ‘ ‘ + mt.col1 FROM ManyTable mt WHERE mt.foreignkeycol = OneTable.primarykeycol FOR xml path(”) ), 1, 1, ” ) AS concatenatedcol FROM OneTable Note: remember the ” in the inner SELECT statement, otherwise … Continue reading

Posted in SQL | Leave a comment