sql

n-grams from text in PostgreSQL

I am looking to create n-grams from text column in PostgreSQL. I currently split(on white-space) data(sentences) in a text column to an array. enter code hereselect regexp_split_to_array(sentenceData,E'\s+') from tableName Once I have this array, how do I go about: Creating a loop to find n-grams, and write each to a row in another t...

How to create a MySQL query for time based elements with a 'safe window'?

I am no SQL expert, far from it. I am writing a Rails application, and I am new at that as well. I come from a desktop programming background. My application has a table of data, one of the columns is the time at which the data was logged. I want to create a query with a 'safe window' around EACH row. By that I mean, it returns the ...

Design pattern for creating an XML document from a database query that has a 1:many relationship

All, Sorry in advance for the ambiguous title. Here's what I'm trying to do... I'm working on an application that needs to query a database to get a list of items. That list of items will get formatted into an XML document that gets returned to the querying front-end. To simplify, let's say that the items are books. Each book has the...

SQL LEFT JOIN help

My scenario: There are 3 tables for storing tv show information; season, episode and episode_translation. My data: There are 3 seasons, with 3 episodes each one, but there is only translation for one episode. My objetive: I want to get a list of all the seasons and episodes for a show. If there is a translation available in a specified...

Sql - goto statement

Hi folks, is it good practise to use 'goto' statements in Sql queries? ...

Need some serious help with self join issue.

Well as you may know, you cannot index a view with a self join. Well actually even two joins of the same table, even if it's not technically a self join. A couple of guys from microsoft came up with a work around. But it's so complicated I don't understand it!!! The solution to the problem is here: http://jmkehayias.blogspot.com/2008/12...

Table choosing algorithm

Below I'm using simple example, real tables are bigger, I cannot store devices in one table, I can not merge device1 and device2 tables. I have three tables: device1, device2, computer(device3), device1 and device2 have id from the same sequence, device3 is computer. Table device3 can be connected with device1 and device2 (many-to-many)...

sql Query in MS Access

Trying to get this query to work in MS Access. Update Network.Location=Enclave.Location Where Enclave.Site=No AND Network.AlternateLocation=Enclave.Location Where Enclave.Site=Yes I'm not sure how to get this to do exactly what I want which is store Enclave location in network location if the enclave site field is No and if yes sto...

rails: Get all items tagged x AND y AND z

I've got two models: Item and Tag. Both have a name attribute. I want to find items tagged with several tags. class Item < ActiveRecord::Base has_many :tags validates_presence_of :name end class Tag < ActiveRecord::Base belongs_to :item validates_presence_of :name end Given a list of tag ids, I can easily enough get the list ...

insert a time-stamp value in my db by php

I'm using oracle express and in my application i would insert a time-stamp value in my table: $marca = date('y-m-d H:i:s'); $query = " INSERT INTO SA_VERSIONE ( ID_ACCETTAZIONE, MARCA_TEMPORALE, TESTO, FIRMA, MEDICO) VALUES ('$id', '$marca', '$testo', '$firma', '$medico')...

SQL - Optimizing complex paged search querys

Hi, I'm currently developing a stored procedure for a complex search in a big database. Because there are many thousand entries, that could be returned I want to use paging. Although it is working, I think it's too slow. I read a lot of posts and articles regarding pagination of SQL queries and optimizing performance. But most 'optimizat...

Fastest way to parse XML files in C#?

I have to load many XML files from internet. But for testing with better speed i downloaded all of them (more than 500 files) of the following format. <player-profile> <personal-information> <id>36</id> <fullname>Adam Gilchrist</fullname> <majorteam>Australia</majorteam> <nickname>Gilchrist</nickname> <shortName>A ...

Counting the instances of customers

Say that I have a table with one column named CustomerId. The example of the instance of this table is : CustomerId 14 12 11 204 14 204 I want to write a query that counts the number of occurences of customer IDs. At the end, I would like to have a result like this : CustomerId NumberOfOccurences 14 2 12 ...

Database design efficiency with 1 to many relationships limited 1 to 3

This is in mysql, but its a database design issue. If you have a one to many relationship, like a bank customer to bank-accounts, typically you would have the table that records the bank-account information have a foreign key that keeps track of the relationship between account and customer. Now this follows the 3rd normal form thing and...

in-memory database in Python

I'm doing some queries in Python on a large database to get some stats out of the database. I want these stats to be in-memory so other programs can use them without going to a database. I was thinking of how to structure them, and after trying to set up some complicated nested dictionaries, I realized that a good representation would ...

Question about using ServiceModelEx's "WCFLogbook" from "Programming WCF Services" book

I am attempting to compile/run a sample WCF application from Juval Lowy's website (author of Programming WCF Services & founder of IDesign). Of course the example application utilizes Juval's ServiceModelEx library and logs faults/errors to a "WCFLogbook" database. Unfortunately, when the sample app faults, I get the following new error...

Is there any sql interpreter for objects?

Is there any interpreter that takes a string or even a custom object as input and execute it on my datasource? I cannot use linq to object because query always changes and the report i'm working on, has about 6000 queries which i can reduce to 9 if i find some tool doing that for me. Opensource is very applicable. thanks in advance. ...

Dates that intersect

Hi everyone, I've been researching this problem for awhile now and I can't seem to come to a solution, hopefully someone here can help. Currently I'm working with Microsoft SQL server management, I've been trying to do the following: Previously, the old query would just return the results that fit between two dates Heres the previous ...

help with delete where not in query

I have a lookup table (##lookup). I know it's bad design because I'm duplicating data, but it speeds up my queries tremendously. I have a query that populates this table insert into ##lookup select distinct col1,col2,... from table1...join...etc... I would like to simulate this behavior: delete from ##lookup insert into ##lookup sele...

How to enumerate returned rows in SQL?

I was wondering if it would be possible to enumerate returned rows. Not according to any column content but just yielding a sequential integer index. E.g. select ?, count(*) as usercount from users group by age would return something along the lines: 1 12 2 78 3 4 4 42 it is for http://odata.stackexchange.com/ ...