Preface: I was thinking the other day about a new database structure for a new application and realized that we needed a way to store historical data in an efficient way. I was wanting someone else to take a look and see if there are any problems with this structure. I realize that this method of storing data may very well have been in...
Lets say I have a database that looks like this: tblA: ID, Name, Sequence, tblBID 1 a 5 14 2 b 3 15 3 c 3 16 4 d 3 17 tblB: ID, Group 14 1 15 1 16 2 17 3 I would like to sequence A so that the sequences go 1...n for each group of B. So in this case, the s...
DROP TABLE table1; DROP TABLE table2; CREATE TABLE table1 ( col1 NUMBER, col2 NUMBER, col3 NUMBER, col4 NUMBER ); CREATE TABLE table2 ( col1 NUMBER, col2 NUMBER, col3 NUMBER, col4 NUMBER ); INSERT INTO TABLE1 VALUES (1,44,1,1); INSERT INTO table1 VALUES (2,44,2,2); INSERT INTO TABLE1 VALUES (3,44,3,...
I have a string that always reads as follows: re-assigning LastName, FirstName re-assigned the order to LastName, FirstName (Administrator) Reason Code: Reassign order Comment: The name is always diffent but I only want to return the LastName, Firstname part on the first occurence so right after re-assigning. The spaces and every...
I have an elementary query that is taking too long to execute even on small tables (<100,000 rows): select images.classification, count(boxes.id) from images join boxes on images.id = boxes.image_id where boxes.round = 0 group by images.classification; I have indices on boxes.round, boxes.image_id, and images.classification (only varc...
Hi, all. I have an Oracle table with two columns ID and START_DATE, I want to run a query to get the ID of the record with the most recent date, initially i wrote this: select id from (select * from mytable order by start_date desc) where rownum = 1 Is there a more cleaner and efficient way of doing this? I often run into this pat...
I am trying to get all my post where Vote IPAddress doesnt match mine own. Here is my SQL. The problem is, i get the post i voted for when someone else also voted for it. I thought this code says select post when left join is possible and there are no IPAddr which == mine. But that doesnt seem to be happening. How do i write this? selec...
Hello, I have these 2 mysql queryes SELECT `name` AS name1,`id` AS id1 FROM `table` WHERE id=1 SELECT `name` AS name2,`id` AS name2 FROM `table` WHERE id=2 The results will be outputed like this NAME1 ID1 john 1 NAME2 ID2 meg 2 Is there anyway that from 2 queries I would make 1 and it will show all results on 1 line from sam...
Four column table - id (int), double1 (bit), double2 (bit), score (int) Want to write a query the returns the SUM() of the score column, grouped by id, where each row score can be changed based on the two double columns. So if the score is 10 and double1 and/or double2 columns are true, the row score is doubled once or twice. ...
Someone please change my title to better reflect what I am trying to ask. I have a table like Table(id,value,value_type,data) ID is NOT unique. There is no unique key. value_type has two types. lets say A and B. Type B is better than A, but often not available. For each id if any records with value_type B exsits I want all the reco...
Hi, Suppose I have a table a with one column b and three rows(1,2,3), I would like to create a function that will return '1,2,3' that would be called like this : SELECT FUNC(f), ... FROM ... In other words, I have a linked table that have more than one rows linked to each rows of the first table and would like to concatenate the conten...
I'm trying to set up an automated process to regularly transform and export a large MS SQL 2008 database to MongoDB. There is not a 1-1 correspondence between tables in SQL and collections in MongoDB -- for example the Address table in SQL is translated into an array embedded in each customer's record in Mongo and so on. Right now I ha...
I have a mysql query that's taking several minutes which isn't very good as it's used to create a web page. Three tables are used: poster_data contains information on individual posters. poster_categories lists all the categories (movies, art, etc) while poster_prodcat lists the posterid number and the categories it can be in e.g. one p...
How can I put the results of a query into a table which is NOT created? For example, put the following query results into a new table EmployeeDetail which IS NOT CREATED. Create the table and put the results at the same time. select a.Name, b.Id from Database1 a left join Database2 b ON a.Id = b.Id How can this be done? ...
Ok, So I have these two tables - BioUser- UserId,Weight,DateAdded DimDate-Date // It has basically all the dates for any period..its basically a table with all dates till 2050 Now the BioUser table has entries for weight of a user but not for everyday but whenever they enter their weight. So I want to basically build a list of values...
Hi All, We have a legacy database which is a sql server db (2005, and 2008). All of the primary keys in the tables are UniqueIdentifiers. The tables currently have no clustered index created on them and we are running into performance issues on tables with only 750k records. This is the first database i've worked on with unique id...
I am trying to develop a query to insert unique records but am receiving the SQL Server Primary Key error for trying to insert duplicate records. I was able to insert some values with this query but not for this record (score_14). So now I am trying to find duplicate record with the following query. The challenge is that my PK is ba...
I wanna put the results from sql query into a table but if i do SELECT columns names INTO NEWTABLE(which is not created), its good for one time but when i run that query again, it says table already exists. All i am doing is results from that query into the newtable and i am gonna run it every week so it should not give me the error: tab...
From Oracle: "When you declare a cursor variable as the formal parameter of a subprogram that fetches from the cursor variable, you must specify the IN or IN OUT mode. If the subprogram also opens the cursor variable, you must specify the IN OUT mode." But, I can code that (only OUT parameter): create or replace procedure mycur_out(mc ...
A top class called Parametric is used to create objects which can have parameters associated with them: class Parametric(object): def __init__(self, name): self.name = name self.pars = [] class Foo(Parametric): def __init__(self, name, prop): self.prop = prop Parametric.__init__(self, name) class Bar(Parametric): def __init...