sql

Install Sharepoint on a server with other databases already on it?

We have a DB server with a couple web app db's on there (don't get a ton of traffic). We'd like to make use of the server and allow it to be the DB server for sharepoint. I'm assuming it's not good practice and that sharepoint should have it's own exclusive db server. Am I right in that conclusion, or is it alright if we put the datab...

Can't establish SQL Server connection from VS 2008

Hi, I am sorry if this sounds like a silly question, but I have googled for a while for a solution and no success. I am trying to establish a connection from VS 2008 to SQL Server. I am trying to use an user that my web application has been successfuly using to access the database from in its connection string. But when doing it from VS ...

SQLite for joined tables

How would I convert the following statement from MySQL-ese to SQLite-ese? UPDATE Attribute, Name SET Attribute.AttValue = 'foobar' WHERE Attribute.NameID = Name.NameID AND Name.Name = 'rotate_ccw' It looks like SQLite doesn't support joins in an UPDATE statement. ...

SQL query: get tag id and number of tag occurences

Hi, I think, the answer to my question is rather simple, but I just can't figure it out at the moment. I experimented with DISTINCT() and COUNT(), but had no success, although I know, that I did it somehow once before. So: I have three tables: article, tag, and article_tag. The last one is simply two columns with article_id and tag_id ...

MySQL: Typecasting NULL to 0

Hi all, Let us suppose the following table (e.g. a result of several inner join statements): id | column_1 | column_2 ------------------------ 1 | 1 | 2 | 2 | 2 3 | | 3 Which you could for example get from the following statement: select a.id, t1.column_1, t2.column_2 from a left join t1 on a.id = t1.id le...

Can I limit the amount of rows to be used for a group in a GROUP BY statement

Hi everyone, I'm having an odd problem I have a table with the columns product_id, sales and day Not all products have sales every day. I'd like to get the average number of sales that each product had in the last 10 days where it had sales Usually I'd get the average like this SELECT product_id, AVG(sales) FROM table GROUP BY prod...

Complexity of adding n entries to a database

What the complexity in big O notation of adding n entries to a database with m entries with i indexes in MySQL and afterwards committing? ...

Database Query Slower with View

I kind of have a feel for why the view is slower: The where clause is probably not applied at the same time. The results do seem to be the same, though. I am not sure what I can do about this, short of not using a view...which is not ideal, as I added the view to avoid code repetition, and I don't want to remove it if it isn't necessar...

Wrong SQL for view object using Hibernate Annotations

I'm working on a hibernate entity mapping for a database view; when I do a criteria query against it, hibernate is generating bad SQL. Any help figuring out what the problem is with my mapping would be greatly appreciated! I have two mapped entities which I am trying to grab from a database view; the view has no other columns, just the ...

Sql Server - top 1 post per member ordered by createdDate

I have a table Posts which has a memberID and createdDate. I need to return the most recent post per member and the posts must be order with most recent at the top. I am not sure how to do this with Sql Server, can anyone help? ...

ASP Search and Results in a single page

Hi there, I have a single Classic ASP page that I wish to display a search form and the associated results. When a user first comes to this page, I want to display a search form and the 10 latest properties. If a user decides to use the search form to retrieve more relevant properties, then i want the default 10 latest properties to be...

LINQ 2 SQL: top 1 post per member ordered by created data

Okay so I have the sql to work this out as asked in the stackoverflow question here. Does anyone know how to translate this to linq 2 sql? I'm guessing that the easiest way is to add a stored procedure, but I am curious to see if it can be linq-a-fied :P select p.* from post p join ( select memberId, max(createdDate) as maxd ...

Oracle Sqlplus Problem on Mac OSX Snow Leopard

I just installed the Oracle Instant Client for Mac OSX with the SQLPlus extension and have the following problem. I can connect to a database instance and schema and I can do queries but when I try the "describe command" it just hangs. For example: SQL> SELECT COUNT(*) FROM APPROVABLETAB; COUNT(*) ---------- 8 SQL> desc APPROVABLE...

Oracle: normalized fields to CSV string

I have some one-many normalized data that looks like this. a | x a | y a | z b | i b | j b | k What query will return the data such that the "many" side is represented as a CSV string? a | x,y,z b | i,j,k ...

Show records that contain a certain value first in MySQL

I need the records in my database that are "featured" to be listed first. If a listing is "featured" the value in the featured column is "yes". I am not sure what kind of MySQL Query would give me this result, or if it even exists. But the other idea I have is to have one query that gets all featured ones and lists them, and then anothe...

SQL that can pulls records by a possible three values or none at all

Say you have WIDGETS table: WidgetID int ColorID int (Lookup values: Red, Blue, Green, Yellow, Black, Brown) SizeID int (Lookup values: Small, Med, Big, Large) Weight int (Lookup values: UltraLight, Light, Normal, Heavy, UltraHeavy Ok that's the general idea of the table. I don't need the lookup names, what I do have is the lookupva...

How to return only 1 row if multiple duplicate rows and still return rows that are not duplicates?

I have a temptable that looks like this: RequestID | CreatedDate | HistoryStatus CF-0000001 | 8/26/2009 1:07:01 PM | For Review CF-0000001 | 8/26/2009 1:07:01 PM | Completed CF-0000112 | 8/26/2009 1:07:01 PM | For Review CF-0000113 | 8/26/2009 1:07:01 PM | For Review CF-0000114 | 8/26/2009 1:07:01 PM...

How to update multiple information at different locations(table) on change of a record?

This is not SO Meta question. I am using SO only as example. In StackoverFlow each answer, each comment, each question, each vote has a effect which produces a badge at some point of time. I mean after every action a list of queries are tested. E.g. If Mr.A up votes Mr.B Answer. So we have to check is this Mr.B's answer upvoted 100 ti...

How to SELECT the newest four items per category?

I have a database of items. Each item is categorized with a category ID from a category table. I am trying to create a page that lists every category, and underneath each category I want to show the 4 newest items in that category. For Example: Pet Supplies img1 img2 img3 img4 Pet Food img1 img2 img3 img4 I know that I could easily...

MySQL Query Questions with a Foreign Key Database

Here's some background info. I have three MySQL tables (all InnoDB). The first table is used to store images records. The second table is used to store tags (to tag the images with. OMG). The third table is used to store relationships between the images and the tags. Yippee. Here's the structure of the tables. CREATE TABLE `images` ( ...