Given this LINQ to SQL: using (var db = Database.Context) { var root = (from post in db.Post where post.Id == rootPostId select post).Single(); root.LastActivityUtc = DateTime.UtcNow; db.SubmitChanges(); } What will happen if the same record is concurrently being changed by another call to...
What's the best way to get a sublist of things? I have two tables: create table A ( id int primary key ) create table B ( id int primary key, aid int foreign key references A( id ), sort_key int ) I want to get a list of objects A, with subobjects B, but only the top five of B. Let's say A is people, and B is type of food, ...
Similar to this question, I need to group a large number of records into 1-hour "buckets". For example, let's say I've got a typical ORDER table with a datetime attached to each order. And I want to see the total number of orders per hour. So I'm using SQL roughly like this: SELECT datepart(hh, order_date), SUM(order_id) FROM ORDERS GRO...
I'm having trouble thinking of a way to do the following join in MySQL. I'm not sure which joins would be best suited for this task, so I'll edit the title when someone points it out. Here's the gist of what I'm trying to do. I have two tables, call one Students, and the other Marks. They are setup as follows, Students Only the Id ...
I want to keep only 1000 entries for each clientid. The code below does what I want but does not loop through the clientid, instead keeping 1000 total of any of the clients. Is there a way to do this in sql? I was told i need a cursor, but I am hoping not. SQL 2005 DECLARE @ids TABLE ( id int ) DECLARE @clients TABLE ( clientid varc...
Many years ago, I was asked during a phone interview to delete duplicate rows in a database. After giving several solutions that do work, I was eventually told the restrictions are: Assume table has one VARCHAR column Cannot use rowid Cannot use temporary tables The interviewer refused to give me the answer. I've been stumped ever...
I would like to delete records which are considered duplicates based on them having the same value in a certain column and keep one which is considered the newest based on InsertedDate in my example below. I would like a solution which doesn't use a cursor but is set based. Goal: delete all duplicates and keep the newest. The ddl below ...
Ok, so my schema is this: Table: Timesheet_Hours Columns: Timesheet_Id (PK, int) Staff_Id (int) BookedHours (int) Posted_Flag (boolean) This is an extremely simplified version of the table, but it will serve for the purposes of this explaination. Assume that a person can only ever have one timesheet record. What I'm trying to do i...
I have an sql query that returns a lot of results for which I want to generate an html table to display them. Problem is I don't want to display them all on one page, I want to grab 10 at a time and flip through pages of results. I want to return 100 results for each query but I can't figure out how to get THE NEXT 100 on the next quer...
Does anyone have an elegant sql statement to delete duplicate records from a table, but only if there are more than x number of duplicates? So it allows up to 2 or 3 duplicates, but that's it? Currently I have a select statement that does the following: delete table from table t left outer join ( select max(id) as rowid, dupcol1, dup...
Im wondering about the real advantage of performing dml commands (inserts, updates, deletes) in the database via stored procedures for simple CRUD applications. Whats the beneffit with that appoach over just using some generic procedure in the front-end that generates the dml commands? Thanks in advance. ...
The following User History table contains one record for every day a given user has accessed a website (in a 24 hour UTC period). It has many thousands of records, but only one record per day per user. If the user has not accessed the website for that day, no record will be generated. Id UserId CreationDate ------ ------ ----...
With only a bit of previous experience with databases and no formal education with them, I'm a bit stuck as to how to model this (and retrieve the data I require from it in PHP). This is what I'm trying to model: For each item on my site, it is allowed to have multiple tags such as file, upload, php, recursive etc. However the tags are ...
Hi , What's better way to format following sql statement considering both readability and performance. Thanks. sql = (char *)" SELECT * ,rowid FROM tblEvent_basic " " WHERE " " service_id = ? AND " " (" " (start_time >= ? AND start_time <= ?) OR " ...
Hello everyone, How do I group a set of tables or label them together? In our project,each user has two separate tables allocated for them. I need to group all users tables separately. How do I accomplish this? Thanks. ...
How do I get the number of rows affected by a BULK INSERT on SQL Server (2008 if that matters)? ...
Hi all, I want to check if there are any records in a table for a certain entry. I used COUNT(*) to check the number of records and got it to work. However, when the number of records for an entry is very high, my page loads slowly. I guess COUNT(*) is causing the problem, but how do I check if the records exist without using it? I onl...
Jeff recently asked this question and got some great answers. Jeff's problem revolved around finding the users that have had (n) consecutive days where they have logged into a system. Using a database table structure as follows: Id UserId CreationDate ------ ------ ------------ 750997 12 2009-07-07 18:42:20.723 750998...
Hi Im struggling a bit with this and could use some ideas... Say my database has the following tables ; Customers Supplers SalesInvoices PurchaseInvoices Currencies etc etc I would like to be able to add a "Notes" record to ANY type of record The Notes table would like this NoteID Int (PK) NoteFK Int NoteFKType Varc...
i know crystal reports very well and i work on this for last 3 years. I wanted to learn Reporting services of sqlserver so which is the best resource to learn the Reporting services quickly ? ...