Similar to a feed reader, I'm storing a bunch of articles, each pertaining to a source (feed) and each feed can belong to a category. What I'm trying to do is: Retrieve the articles of the feeds that belong to a certain category. Group the articles. One scenario would be by date(published_time), so that I have groups, for example: (12....
Hi, I had a look on the site and on Google, but I couldn't seem to find a good solution to what I'm trying to do. Basically, I have a client server application (C#) where I send the server an SQL select statement (Connecting to SQL Server 2008) and would like to return results in a CSV manner back to the client. So far I have the follo...
Hi! I need something like this... stored value in DB is: 5XXXXXX [where x can be any digit] and i need to match incoming SQL query string like... 5349878. Does anyone has an idea how to do it? i have also different cases like XXXX7XX for example, so it has to be generic. i don't care to represent the pattern in a differnet way ins...
I have a table that looks like this user_id | name | created_on | updated_on -------------------------------------------------- 1 | Peter D | 1/1/2009 | If I insert or update a record, I'd like a trigger to update the updated_on field with datetime('now'). But I can't find the function name to target the most re...
So I am pulling a list of links and I am attempting to sort these links by popularity. I am using the Hacker News algorithm: Y Combinator's Hacker News: Popularity = (p - 1) / (t + 2)^1.5 Votes divided by age factor. Where p : votes (points) from users. t : time since submission in hours. p is subtracted by 1 to negate submitter's ...
example tag { id PK, name TEXT }; item { id PK, exampleData LONG, tagId LONG //or foreign key depending on provider } i'm using SQLite and C#. Is it proper to Start a transaction Insert into tag If affected rows = 0 do a select to get tag.PK otherwise use last_insert_rowid Insert item or will there be a problem with the above? I ...
I found exactly what I need in MySQL's TIMESTAMPDIFF() but I am having a hell of a time trying to find an equivablent with PostgreSQL. I know I can subtract the two timestamps to get an INTERVAL but I am having a hard time wrapping my head about what it means and specifically I just want the difference between the two timestamps in in h...
Lets say I have a table with some data like the following: ID text OtherID _______________________ 6 text1 24 7 text2 24 8 text3 24 9 text1 25 10 text2 25 As you can see I have multiple entries with the same OtherID. what would be an sql statement that would select only the newe...
I need to create a view that automatically adds virtual row number in the result. the graph here is totally random all that I want to achieve is the last column to be created dynamically. > +--------+------------+-----+ > | id | variety | num | > +--------+------------+-----+ > | 234 | fuji | 1 | > | 4356 | gala ...
Hi... When using LOAD DATA INFILE, is there a way to either flag a duplicate row, or dump any/all duplicates into a separate table? ...
I want to select all the fields where a certain column is not a value I specify. I tried this but it didn't work. SELECT * FROM table WHERE columnname != value My mistake. It was another error so I thought it was wrong with != operator cause it was the first time I use it. Sorry guys! ...
I've recently started to work with Linq to SQL and wondered how to get multiple rows as a result of executing a stored procedure,here's a simple sp i want to work with: CREATE PROCEDURE gsp_ftsmultiple @SearchKey varchar(100) AS BEGIN SET NOCOUNT ON; SELECT Label, theContent FROM FtsTest WHERE FREETEXT( theContent, @Se...
Hi All, For about a year now, we’ve been allowing our users to login with usernames and/or email addresses that are not unique (though each user does have a unique id). Although the system handles duplicate usernames/emails elegantly, we’ve decided to finally enforce unique usernames and email addresses. I’ve been tasked with generati...
Its been habitual in most of the scenarios while developing a database design we set primary key as integer type for a unique identifier in the Table , Why not string ,float type for primary keys does this affect the accessibility of values or in plain words retrieval speed of values from the table will be slower ? Are there any specif...
In have entities: Collection and Item. Each collection has many items. Both Collection and Item can be edited collaboratively by all users of the application. I have to provide a way to undo changes. Versioning Item records is straightforward, the difficult part is to version the relation between the Collection and Item. The allowed op...
Why do I get two times "ANSI" and not the first time "ANSI" and the second time "AnyData"? #!/usr/bin/perl use warnings; use strict; use 5.010; use SQL::Parser; my $parser = SQL::Parser->new(); my @dialects = $parser->list( 'dialects' ); say "available dialects : @dialects"; # AnyData CSV ANSI my $...
I have two database tables, 'Lists' and 'Notes'. Lists has columns _id, listname Notes has columns _id, checked, list_id (which is a foreign key of Lists._id), and more columns that aren't relevant to this question. I would like to create a query that returns four columns: Lists._id, Lists.listname, the count of all checked Notes in thi...
Possible Duplicate: C# Update Table using SqlCommand.Parameters I'm trying to update an SQL Server table using SqlCommand, I think it's a syntax error with my T-SQL, but here is what I have so far: SqlCommand sqlCmd = new SqlCommand( "UPDATE yak_tickets SET email = @emailParam, subject = @subjectParam, text = @textParam...
Hello guys. I have two tables : create table CurrentDay ( ID int identity primary key, ssn varchar(10), val money, CheckDate datetime, CurrentStatus tinyint) create table PreviousDay ( ID int identity primary key, ssn varchar(10), val money, CheckDate datetime, CurrentStatus tinyint) I need update field CurrentDay.CurrentStatus...
I'm trying to model a site similar to StackOverflow. It has a set of users and questions, and user vote on questions. Each user can only have one vote on each question. What should the structure of my "VotesOnQuestions" table look like: Should I have an auto-generated "VoteID" column? How do I disallow, at the schema level, a user fro...