sql

Rails ActiveRecord Problem With Complex Join - Select Does Not Work

Hello, I have two model objects: Event Venue Events have Venues. Venues can have 1..* events. Venues have a location, a lat and long, which I use with the Geokit Rails plugin. Here's what these models look like in Rails: class Event < ActiveRecord::Base belongs_to :venue acts_as_mappable :through => :venue end and class Ven...

MySQL: use the id of the row being inserted in the insert statement itself

I'm trying to some something like that: INSERT INTO dir_pictures SET filename=CONCAT(picture_id,'-test'); picture_id is my primary key, auto-increment. Basically I'm trying to put the id of this insert statement, in the statement itself. I'm sure it can be done with some extra PHP code or using more than one statements, but I was won...

multiple cte in single select statement where ctes can refer to each other ...

Expanding on following question (Multiple Select Statement) I would like to know if I can do following: WITH cte1 as ( SELECT * from cdr.Location ), cte2 as ( SELECT * from cdr.Location WHERE cdr.Location.someField = cte1.SomeField ) select * from cte1 union select * from cte2 So accent here is on ...

Seek & Scan in SQL Server

After googling i came to know that Index seek is better than scan. How can I write the query that will yield to seek instead of scan. I am trying to find this in google but as of now no luck. Any simple example with explanation will be appreciated. Thanks ...

MySql table design: multiple tables vs. multiple columns

I'm stumped on the ideal method of dealing with a relatively large number of table fields, and whether or not they should be split off into separate relational tables. I've got a set of tables for a web-based game of sorts, holding player data, item data, class data, etc. For each one of these (player/item/class) I also need to record a...

Why cursor show error

work on SQL Server 2000. want to Automated Email Notifications using SQL Server Job Schedular.If i run the bellow cursor syntax in query analyzer than i get no error .But when i set this syntax on SqlServer agent->Jobs then it's create error. -- Script generated on 29-Oct-09 11:57 AM -- By: sa -- Server: (LOCAL) BEGIN TRANSACTION ...

How to select unique records by SQL

When I perform "SELECT * FROM table" I got results like below: 1 item1 data1 2 item1 data2 3 item2 data3 4 item3 data4 As you can see, there are dup records from column2 (item1 are dupped). So how could I just get result like this: 1 item1 data1 2 item2 data3 3 item3 data4 Only one record are returned from the duplicate, along with...

How to filter the columns?

Using SQL Server 2005 I want to filter the column where column value not equal to numeric Table1 Status 010203 Absent 231415 Ramesh Suresh ..., I want to get only Names from the status column like Tried Query Select Status from table1 where status <> 'absent' and status <> numecivalue How to mentioned status <> numericvalue E...

Checking timestamps in PL/pgSQL

If I have a function in PL/pgSQL that takes in a timestamp, what is the best way to identify whether that date is less than 12 months in the past? e.g. CREATE FUNCTION do_something(foo timestamp) .... -- IF foo is less than 12 months in the past THEN -- do something -- END IF; END; ...

A loop until in the stored procedure

I am trying to write a procedure that will fire the same select query till the number of results are more than 0. If the "interval 2 hour " returns 0 records then the "interval 4 hour" criterion should be used and if there are still no records fetched, then lastupdate > current_date() should be used in the where clause. These are the 2...

Updating one mysql table based on calculations using variables in another table

I'm trying to update one table based on values in another table. What's wrong with the following request? Error: Unknown column 'source.col3' in 'where clause' UPDATE target SET target.col1 = source.col1 * target.col2, WHERE target.col3 = source.col3 ...

Is there such thing as a SELECT with an IF/ELSE statement in mySQL?

Is it possible to have a SELECT statement in mySQL with an IF/ELSE clause in it? E.g. I want to SELECT all table rows and return color2 ONLY if color2 is 'brown'. My example SQL select statement of what I would like to accomplish is below. APPLES +------+--------+----------+----------+ | ID | NAME | COLOR1 | COLOR2 | +--...

How do I select min/max dates from table 2 based on date in table 1?

I have a monthly table (only holds rows with first day of month, and unique constraint so only one of each) and a daily table with similar information for each day (same deal, only one per day): Monthly table Daily table ------------- ----------- 2009-01-01 2009-01-01 2009-02-01 2009-01-02 : : ...

LINQ and Grouping from a simple SQL DB relationship

I have two tables with the following layout and relationships: Tasks: TaskID StartTime EndTime TaskTypeID ProductionID ------------------------------------------------------------ 1 12:30 14:30 1 1 2 14:30 15:30 2 1 3 11:10 13:40 2 ...

How can I use a mySQL SELECT IF statement row to JOIN another table?

I am using an IF statement in my mySQL SELECT statement and depending on the returned result from the IF statement I would like to join another table. Eg. SELECT name, IF(apple = 'brown', color1, color2) AS ripeness FROM apples JOIN apple_type ON apple_type.color = ripeness My problem is that I am receiving the error msg: Unknown colum...

The timeout period elapsed prior to obtaining a connection from the pool

Our site works fine for 90% of the day, then during our peak hours when traffic is about twice as heavy as normal, everything slows down to a crawl. Page load times that are normally 1 second take 30 seconds. Checking our error logs, it looks like it may be a connection pool issue. We have 3 web servers connected to 1 sql server db. ...

SQL query and datetime parameter takes long time to execute

I have a query which takes datetime as a parameter, what we have observed is that if you supply datetime parameter through a variable, Query takes 2 -3 times more time to execute than if you directly hardcode the parameter, Is there any reason or solution to it Following query takes around 5 mins to return the result Declare @Date as D...

SQL complex query

Hi, I have two tables: 'Category' and 'Product'. In Category I have catid and cat name. In product table I have item-id, item-name, catid. What I need to do is show a result that will have item name and category name. But the category name will be multiple. So that one product can have multiple categories on it. ...

Return random rows from the best ones (i.e: 100 rows -> best 10 -> get 5 random)

Hello all again! Situation: A table with lots of photos, classified and with votes. I'm currently doing this: SELECT n.nid, n.title, ....... order by v.value desc limit 0,5 What I want is to get a random resultset after sorting my rows by its votes. Now, a table with hundreds of records is giving me the best 5 rows. Always the same 5...

Oracle Collect Over Analytical Function

Is it possible to use the 10g collect command as an analytical function by using OVER PARTITION or some other way? e.g. SELECT COLLECT(x) OVER (PARTITION BY y) FROM table Every time I try this there is a ora 3113 exception saying 'end-of-file on communication channel' PS. I know I need to cast the result to make it useful, but for s...