Extending tables in SQL
How to add a column to the table: create table table1 (id integer primary key, field1 text) ? The column would be field2 text and the value for existing rows in this column should be value. ...
sql
SQL GROUP_CONCAT + SUM + AVG
SELECT *, GROUP_CONCAT(rating) AS rating_total, SUM(rating_total) AS rating_sum, AVG(rating_sum) AS rating_avg FROM ratings GROUP BY pid For some reason the sum and average don't execute....how do you make this statement work? ...
LINQ query for a forum
Hi, I'm coding this forum and since I'm new to LINQ I ran into this problem when the user hits the main page. I want a table displaying a list of forums like this: Forum --- Topics (count) --- Posts (count) --- LastPostUserId --- LastPostTime I have the following SQL tables: Forums: ForumId (int32), Title (string), Description (str...
Assigning data-entry tasks to multiple concurrent web-app users
I'm trying to think of an efficient way to allow a group of people to work through a queue of data entry tasks. Previously we've just had one person doing this so it hasn't been an issue. The back-end is an RDBMS and the front-end is a web-application. Currently we do something like this: To assign a record for editing: SELECT * FRO...
fetching single child row based on a max value using Django ORM
I have a model, "Market" that has a one-to-many relation to another model, "Contract": class Market(models.Model): name = ... ... class Contract(models.Model): name= ... market = models.ForeignKey(Market, ...) current_price = ... I'd like to fetch Market objects along with the contract with the maximum price of ea...
mySQL field with a dashed line under it.?
can anyone please explane what does that dashed line stands for i am using phpmyadmin field are name type NULL default longtitude float No 0.001 latitude float No 0.001 thank you. ...
Where should I start with a facebook application?
Hi all. I am thinking about developing a fairly simple facebook application which i have modelled the database in access. I have web space and mySQL databases available, but I was wondering if anyone could point me towards any guides for facebook development? I had a search a while back, but they all seemed rather out of date. Anyone kno...
How do I get a maximium daily value of a numerical field over a year in SQL
How do I get a maximium daily value of a numerical field over a year in MS-SQL ...
How can I fix up this Identity int on my DB table?
Hi folks, A week or so ago some data got imported into a db table. Simple table: Id INT PK NOT NULL IDENTITY Name VARCHAR(20) Now, i noticed today that the first id starts at 0. wtf? asking the kid, he was reseeding it during his testing. So the first entry was a zero. Was an accident. Anyways, I'm not sure how to best update the val...
SQL Server Database securing against clever admins?
I want to secure events stored in one table, which has relations to others. Events are inserted through windows service, that is connecting to hardware and reading from the hardware. In events table is PK, date and time, and 3 different values. The problem is that every admin can log in and insert/update/delete data in this table e.g...
Select many fields applying DISTINCT to only one particular field
In SQL Server, how can I achieve selecting many fields (without an agregation function) and apply the DISTINCT statement to only one particular field? For instance: if I have a table where I store user actions, the pseudo-schema would be like this: UserActions ------------ id, User, Action insertDate I want to get the latest actions ...
SQL Join and Count can't GROUP BY correctly?
Hi, So let's say I want to select the ID of all my blog posts and then a count of the comments associated with that blog post, how do I use GROUP BY or ORDER BY so that the returned list is in order of number of comments per post? I have this query which returns the data but not in the order I want? Changing the group by makes no diff...
What is this SQL IF doing with the Ampersand?
What is this SQL IF doing with the Ampersand? IF ((@TablesToDeleteFrom & 1) <> 0 AND (@TablesToDeleteFrom & 2) <> 0 AND (@TablesToDeleteFrom & 4) <> 0 AND (@TablesToDeleteFrom & 8) <> 0 AND (EXISTS (SELECT UserId FROM dbo.aspnet_Users WHERE @UserId = UserId))) BEGIN ... This ...
How to merge two linq list queries of different type into list of new type?
I want to merge two lists of different types into one list of new type. I would use join, but if for example List A doesnt have a value for that common property, i still want to use the values for List B. class A{ decimal AValue DateTime Date int UserID } class B{ int BValue DateTime Date int UserID } class Merge{ decimal? A...
Ratio of matching rows per group
I'd like to calculate the ratio of items in a group that fulfil certain criteria out of the total number of items in that group. I've already solved this but am curious to know if my solution is optimal, as the query takes a problematically long time over my large (10m+) dataset. Here is what I have in its simplest form: create table #...
WPF SQL Distributed Data Model with LINQ and SQL Express
I am developing an application in C#/WPF that requires a distributed data model, as it will have both online and offline access. My current thoughts are to develop the first version of the application against SQL Server express and LINQ to SQL. Then use the schema to create a SQL Express Compact DB (and modify the connection string) for...
SqlTableProfileProvider and custom type property
I'm using the SqlTableProfileProvider to store profile data. It works great for standard fields... But I would like to store shopping cart data stored like this: http://csharpdotnetfreak.blogspot.com/2009/05/aspnet-creating-shopping-cart-example.html I'm basicly running into the same problem described in this forum topic: http://forums...
Use Linq to get data from a database based on data in another database
I have a database called MasterDatabase that has table MainIndex with columns Id, Database (nvarchar), Table(nvarchar) and I have 2 other databases with tables and data. Is there a way to substitute the FROM statement with results from the MasterDatabase.MainIndex? Can this be done with LINQ? ...
How can I write a SQL count with group by clause using Propel ORM in Symfony?
How can I write the following SQL query using the Propel ORM? SELECT species, COUNT(*) FROM Bird GROUP BY species; ...
MS-SQL In statement
Select a, b, c from table where a in (1, 2, 3) What if the list is in a column? I try this will error: Select a, b, c from a in b Sorry for not clear my question. It's not about join or in (select b from table) Column b type is nvarchar, data is a list, like this '1,2,5' Column a type is int. ...