sql

pysqlite, query for duplicate entries with swapped columns

Currently I have a pysqlite db that I am using to store a list of road conditions. The source this list is generated from however is buggy and sometimes generates duplicates. Some of these duplicates will have the start and end points swapped but everything else the same. The method i currently have looks like this: def getDupes(self)...

How do I import Spanish into a SQL DB?

So I have some Spanish content saved in Excel, that I am exporting into a .csv format so I can import it from the Firefox sql manager add-on into a .sql db. The problem is that when I import it, whenever there is an accent mark, (or whatever the technical name for those things are) Firefox doesn't recognize it, and accordingly produces a...

How can I optimize this query for time performance?

I am working with a large amount of data: 6 million rows. I need the query to run as fast as possible, but am at a loss for further optimization. I already removed 3 subqueries and moved it from 11+ hours to just 35 minutes on a modest dataset of 100k rows. See below! declare @UserId uniqueidentifier; set @UserId = '936DA01F-9ABD-4d9d-8...

c# Subsonic 2.1 : Order by case

Hi All, I would like to know if its possible to order by a specific ID in subsonic 2.1. Same as regular sql query: ORDER BY CASE WHEN ID = 5 then 1 WHEN ID = 10 then 2 WHEN ID = 1 then 3 WHEN ID = then 4 else 5 END I hope someone can help me, I want this functionality for country specific content. Kind regards, Mark ...

Have unpivot automatically grab column list (oracle 11g)

This is a follow up question to http://stackoverflow.com/questions/2344403/transpose-one-row-into-many-rows-oracle I want to be able to unpivot an arbitrary query result. To unpivot a table manually, I would do: select value_type, value from ( ( -- query to be unpivoted -- EG: select col1, col2, col3, col4, col5 from table ) ...

SQL query to pull multiple entries with same user_id

I have a table that contains id, created, user_id. I'm wondering if there is a query that'll return all unique user_ids that have more than one entried created within the last week. I started with: $query = "SELECT distinct user_id FROM task where created >= '" . date("Y-m-d", strtotime("-1 week", strtotime($date))) . " 00:00:00' and c...

SQL Server day datatype?

I am creating a table for an application that handles scheduling and deals with recurring events. I need to indicate which days and times an event may recur on (eg. every Monday and Wednesday at 1pm). Is there a datatype that can handle only days of the week without inputting specific dates, or will I need to create another table conta...

ASP.NET SqlConnection Timeout issue

Hello all, I have run into a frustrating issue which I originally thought was a connection leak but that does not seem to be the case. The secnario is this: the data access for this application is using the Enterprise Libraries (v4) from Microsoft. All data access calls are wrapped in using statements such as using (DbCommand dbC...

What is wrong in this sql select statement?

Here is my select statement, SELECT TOP 1 EmpId, RemainingAdvance FROM SalaryDetails WHERE EmpId IN (SELECT Emp_Id FROM Employee WHERE Desig_Id='27') ORDER BY CreatedDate DESC When i executed SELECT Emp_Id FROM Employee WHERE Desig_Id='27' the results were Emp_Id 16 17 But when i execute my...

naming convention for module specific schema

Our project constitutes of many modules (e.g. a HR management system containing modules like attendance, identity, reports ...) What would be a good naming convention for all the tables represented here.. Would you suggest having the same prefix for all modules (e.g. hrms_ide_user, hrms_rep_reports ..) OR something like ide_user, rep_r...

Merge tables by inserting null values for columns where no value can be derived from the merging tables?

I have two different tables and i decided to merge them into one table... the birth table has id, name, country, birthday_date, description, link the death table has id, name, country, death_date, description, link and i want to merge them into a single table with the structure id, name, country, bdate, ddate, desc...

SQL SELECT speed int vs varchar

I'm in the process of creating a table and it made me wonder. If I store, say cars that has a make (fx BMW, Audi ect.), will it make any difference on the query speed if I store the make as an int or varchar. So is SELECT * FROM table WHERE make = 5 AND ...; Faster/slower than SELECT * FROM table WHERE make = 'audi' AND ...; or w...

SELECT with conditions for preferential source

I have a query which I've been using for sometime however I have a new scenario which I'm not sure how to handle. The query below extracts avg pricing from a table called availables. The problem I have now is that, the data which is contained in this table can come from one of two sources. So I added a source field to the table. Th...

How to delete a record when two tables have foreign key referenced to each other?

Delete any record of them will report an error like this: ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails How to overcome this problem? ...

Hierarchical Data in MySql

I have table with parent child relation I want an help on recursive query table structure roleId, roleName, parentId 1 Admin 0 2 Backup Admin 2 3 Office User 1 0 4 User 1 3 5 User 2 3 6 Office User 2 0 7 Off User 1 6 I am trying to make recursive ...

Make one field unique depending on another field

hello there.. I have the following MySQL table.. userid - foreign key date - primary key slot1 - 1st free slot slot2 - 2nd free slot slot3 - 3rd free slot slot4 - 4th free slot slot5 - 5th free slot slot6 - 6th free slot slot7 - 7th free slot slot8 - 8th free slot All the slots are for the same day, so the user can input di...

spring batch insert using hibernateTemplate, JdbcTemplate

I have a few questions related to batch insert in Spring. When I do something like that: public void save(Car car) { String sql1 = "insert into Car values (1, 'toyota')"; String sql2 = "insert into Car values (2, 'chrysler')"; String sql3 = "insert into Car values (3, 'infinity')"; String[] tab = new String[2]; tab[0] = sql1;...

SQL Query to Find tuples with maximum number of identical values

Any ideas how to implement the query: You are given a relation with N columns of the same type. Write an SQL2 query that returns the tuples having the maximum number of identical values. The query should have size polynomial in N. ...

Transform my Flex application into Adobe Air. Running a PHP in Air

Hi, I'm a newbby in Adobe AIR/Flex. I have developed a simple Flex web app. that through 2 Php's, can connect to a DB and return an XML result of the query. I uploaded the PHP and Flex files to my internet server, and the app. works fine. My question is: How can I make the same app. work in Adobe AIR? In other words, I have made a new...

How to manage "paging" with Solr?

I have a classifieds website... I have Solr doing the searching of the classifieds, and then return ID:nrs which I then use to put into an array. Then I use this array to find any classifieds in a MySql db where the ID:s match the ID:s in the array returned by Solr. Now, because this array can be very very big (100thousand records or mo...