This is a follow up to this question: http://stackoverflow.com/questions/2930256/unique-responses-rails-gem I'm going to create an index based on the user id, url and a date type. I want date type (not datetime type) because I want the day, the 24 hour day to be part of the index to avoid duplication of page views counts on the same da...
Hi I am using C# and using SqlBulkCopy. I have a problem though. I need to do a mass insert into one table then another mass insert into another table. These 2 have a PK/FK relationship. Table A Field1 -PK auto incrementing (easy to do SqlBulkCopy as straight forward) Table B Field1 -PK/FK - This field makes the relationship and is a...
I would like to construct a query that fetches results that occurred between NOW and 15 minutes ago, im getting a mysql error when I try the following , can you help me? thanks SELECT * WHERE user_id = '000' AND date_time < now( ) AND date_time > DATE_SUB( now( ) , INTERVAL 15 MINUTE) Error message: You have an error in yo...
Hellow guys. I am trying to update mysql data by letting users submit a form. The user could update the result of the game and the winning team get +1 win in mysql. The user can always see the game form and update/change the game. My question is how to prevent second or third time +1 win to the existing winning team when the users...
I have an SQL table, from which data is being deleted. Nobody knows how the data is being deleted. I added a trigger and know the time, however no jobs are running that would delete the data. I also added a trigger whenever rows are being deleted from this table. I am then inserting the deleted rows and the SYSTEM_USER to a log table, ho...
Is there a way to determine whether a record was matched or not (whether the record was inserted or updated) after calling MERGE? Ideally I'd like to output it to a parameter. Edit: I've got the merge statement outputting what happened in my management studio using the following statement: Say I had the following merge statement: MERG...
I have three columns in a table - ID, Column1, Column2 - with this example data: ID Column1 Column2 ---------------------- 1 1 2 2 2 1 3 4 3 4 3 4 Since, in the first two rows, Column1 and Column2 have the same values (but in different columns), I want my MAX query to return an ID of 2. Same thi...
I have a simple view in my MSSQL database. It consists of the following fields: report_id INT ym VARCHAR -- YYYY-MM keyword VARCHAR(MAX) visits INT I can easily get the top 10 keyword hits with the following query: SELECT TOP 10 * FROM top_keywords WHERE ym BETWEEN '2010-05' AND '2010-05' ORDER BY visits DESC Now where it gets tric...
Hi there, I'm building a CMS for my own needs, and finished planning my database layout. Basically I am abstracting all possible data-models into "sections" and all entries into one table. The final layout is as follows: Database diagram: I have yet to be allowed to post images, so here is a link to a diagram of my database. Entries ...
Edit Ok I solved all my problems but one. Is it possible to have a hardcoded value insert as well. Like I said I am trying to insert one row(some of its columns) into another table that has about 80% of the same columns however the remaining ones hard non null columns and need some value to be inserted into them. I am wondering can I s...
There is probably an obvious answer to this question, but I am having one heck of a time getting anywhere with it. Consider the query: SELECT * FROM reports AS r JOIN reportvalues AS rv ON rv.report_id = r.report_id JOIN metrics AS m ON m.metric_id = rv.metric_id WHERE r.report_id NOT IN( SELECT DISTINCT report_id FROM exclude...
Background Users can pick dates as shown in the following screen shot: Any starting month/day and ending month/day combinations are valid, such as: Mar 22 to Jun 22 Dec 1 to Feb 28 The second combination is difficult (I call it the "tricky date scenario") because the year for the ending month/day should come after the year for th...
Should I invest a lot of time trying to figure out an ORM style implementation, or is it still common to just stick with standard SQL queries in python/pylons/sqlalchemy? ...
I'm looking for some sample database data that I can use for testing and demonstrating a DB tool I am working on. I need a DB that has (preferably) many tables, and many foreign key relationships between the tables. Ideally the data would be in SQL dump format, or at least in something that maintains the foreign key references, and cou...
SELECT ... WHERE COL IN(A,B) or SELECT ... WHERE (COL = A or COL = B) I'm trying to find out what are the differences between the two constructs? Would there be significant performance gains either way if utilized on resultsets that are nearing the 1 million mark? ...
I hope I'm explaining this properly, my knowledge of MySQL is quite limited. Let's say I have a table with rows that have a field called shape. I'd like to select a bunch of rows from a table, but return all of the rows with unique shape field values first. If I have less than a certain number of rows, let's say 7, then I'd like to f...
Possible Duplicate: Does SQL Server 2005 have an equivalent to MySqls ENUM data type? Is there any way to define ENUM in SQL Server 2005? I have fixed values which I need to use in procedures and functions. ...
Is it better to use foreign keys in tables or can the same results be achieved with joins? ...
I am trying to update a record in the target table based on the record coming in from source. For instance, if the incoming record is present in the target table I would update them in the target else I would simply insert. I have over one million records in my source while my target has 46 million records. The target table is partitione...
I want to add a where clause to make sure video_count is greater than zero. Only categories which are referenced once or more in video_category.video_id should be returned. Because video_count is not a field in any table I cannot do this. Here is the query. SELECT category . * , ( SELECT COUNT( * ) FROM video_category ...