sql

SQL: How do I use parameter for TOP like in SELECT TOP @amount?

In the vs2008 query builder, I’m trying to make a query that gets a parameter for the "TOP" Command, but it won't let me, and it say's "Error in top expression" Works: SELECT TOP 5 * FROM dbo.SomeTable WHERE SomeColumn = SomeValue Doesn't Work: SELECT TOP @param1 * FROM dbo.SomeTable WHERE SomeColumn = SomeValue ...

sql Query help - little help

Short database description "Ships": The database of naval ships that took part in World War II is under consideration. The database has the following relations: Classes(class, type, country, numGuns, bore, displacement) Ships(name, class, launched) Battles(name, date) Outcomes(ship, battle, result) Ships in classes are arranged to a...

SQL Server getdate() to a string like "2009-12-20"

In Microsoft SQL Server 2005 and .NET 2.0, I want to convert the current date to a string of this format: "YYYY-MM-DD". For example, December 12th 2009 would become "2009-12-20". How do I do this in SQL. The context of this SQL statement in the table definiton. In other words, this is the default value. So when a new record is creat...

SQL Server 2008: Filtering Out Duplicate Rows Based on Multiple Columns

Hi, Thanks to everyone who helped me with my last question. This is a similar question, but now I have a better idea of what I want. Again I'm using MS SQL Server 2008, and I'm trying to figure out a query that is a little beyond my week of SQL experience. Right now I have the following simple query SELECT pl.val, pl.txt_val, pl.id FR...

Sql = want to use make an inner join statement but not with tables

Hello my friends, I want to use the fact that on two tables t1 and t2 I can make an inner join with on t1.colum1>t2.colum2 to calculate the maximum drawdown of a return vector. The problem is that an inner join is only possible with two stored databases or tables and I wanted to do it selecting just a part of the tables. Is there any ot...

How do I install Zend on my website and how do I test if it works ?

Background: I need to setup a search engine for my classifieds site, which is PHP and Mysql based. I have downloaded Zend Framework and have no clue what to do with it. My webhosting provider says they don't support changing the php.ini file, and all tutorials I have found so far is based on changing that file. I have found very little...

Custom "WHERE" in stored procedure (Informix)?

I'm doing the specification for an application that has to search a table with different conditions in the WHERE clause. For example (not an actual table): type 1 select name from employees where active = true; or type 2 select name from employees where idBoss = 3; I would rather create one stored procedure with a parameter "type...

Mysql selecting rows from multiple tables

I'm working on a catalog site where users can browse categories. Categories can contain other categories and products, and products can belong to more than one category. The relevant database schema looks something like this: CREATE TABLE products ( product_id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, product_title VARC...

Complex SQL query across habtm join table with ordering in rails

I have three tables: Posts Keywordings Keywords Relevant fields in parens. A Post has_many :keywordings has_many :keywords, :through => :keywordings A Keywording(post_id, keyword_id) belongs_to :post belongs_to :keyword A Keyword(name) has_many :keywordings has_many :posts, :through => :keywordings I want to find all posts t...

Error in Postgres execute

I'm using this function... -- Function: dbo.sp_acc_createaccount(character varying, integer, integer, character varying, character varying, character varying, character varying) -- DROP FUNCTION dbo.sp_acc_createaccount(character varying, integer, integer, character varying, character varying, character varying, character varying); CR...

MySQL like query runs extremly slow for 5000 records table.

I have this issue on our production server. The application stack is, Java Web App on Tomcat 6.0.18 iBatis data access layer MySQL 5.0 database CentOS The system is deployed on virtual server having around 256 MB memory. Real problem: The query like, select * from customer executes in around 10 seconds however if the following q...

MySQL query to get top 10 point-earners in last week?

I'm trying to write a query that returns the user ID's of the top 10 users who gained the most points in the last 7 days on my web app. I have three tables that, together, have the info I need. votingapi_votes table. It has a record for every up/down vote on a comment or node. node table. It can associate a node ID with a user ID so...

q-gram approximate matching optimisations

Hi I have a table containing 3 million people records on which I want to perform fuzzy matching using q-grams (on surname for instance). I have created a table of 2-grams linking to this, but search performance is not great on this data volume (around 5 minutes). I basically have two questions: (1) Can you suggest any ways to improve p...

Subtract all values from data

Table data look like this id val 1 4 2 2 3 1 I want result of subtract valu of val field in one sql statement. like it should be like 4-2-1 = 1 if order by id asc, 1-2-4 = -5 if order by id desc. ...

What is equivalent statement of DBCC INPUTBUFFER(@@SPID)(which give sql statement for current connection otr specified connection) in MYSQL?

What is the MySQL equivalent statement of DBCC INPUTBUFFER(@@SPID), which lists the sql statement for current connection or specified connection? ...

Is it possible to move a record from one table to another using a single SQL statement?

I need a query to move a record from one table to another without using multiple statements? Thanks ...

Regarding delete a record

HI I am having a table which does not have any primary key or unique key. How can I delete the duplicate records? Can any one of u tell me? ...

I have to write two sql statements in order to update a record, is there a better way to do it?

I want to update a table: $result=mysql_query("select balance from tablename where userid='$userid")or die(mysql_error()); $row=mysql_fetch_assoc($result); $accountbalance=$row['balance']; if($accountbalance>$cost) { $result=mysql_query("update tablename set balance-'$cost' where userid='$userid")or die(mysql_error()); } else { ... } ...

Search in sqlite table where column matches regex

Hi, I'm writing a web application in Rails which uses sqlite. I want the user to be able to search a table in the database by using a regex. Is this possible? Thanks. ...

Can an attribute designate one table over another?

I'm creating a sports statistics database. With it, I'd like to catalog game/match statistics for many types of sports. For example, this database would be able to tell you how many touchdowns the Carolina Panthers scored in the 09-10 season (football), or how many free throws were made by the Miami Heat in their last game (basketball). ...