sql

[sqlite] Selecting data from two tables

Hi there, I have a SQL table which has an ID column and some other columns. The second table has also ID column. Now I want to insert into second table rows from first table, but only those that don't appear in it already. I want the second table to be "a sum" of both, but without duplicates. The first one is labels and the second is t...

SQL Server 2005 - Row_Number()

Hi All, I'm trying to understand the unusual behaviour seen when ordering results in a descending order using the row_number() function when using a DISITINCT on the outermost select in my query as below: SELECT DISTINCT (ID), State_Id, Name_Of_Trip, Date_Of_Travel, Creation_Date, Locking_Id, Applicant_Name, Reference_Number, Stat...

Problem taming MySQL query performance with OR statement

[Warning: long post ahead!] I've banging my head at this for quite some time now but can't get on a common denominator what is going on. I've found a solution to workaround, see at the end, but my inner Zen is not satisfied yet. I've a main table with forum messages (it's from Phorum), simplified looks like this (ignore the anon_user_i...

SQL: What is INSERT INTO #table?

I wanted to know what does the # symbol mean? Why is it being used? Example: INSERT INTO #tmpContracts (sym, contractCount) SELECT sym, COUNT(DISTINCT(fulloptionsym)) AS contractCount ...

How can i get the nth row position from table without using limit ?

How can i get the nth row position from table without using limit ? I have a table with four fields id,name,country,description. The query is returning by condition country = 'asia'. The total number of records is more than hundred. Now what i need is if i hav a name 'test' in 23rd position in table then how can i get the position 23r...

MS SQL Server optimizer and varying table and field aliases

We have a lot of quieries for which we append a random alias at the end of field and table names (due to a custom ORM implementation that might be hard to change). The queries are like the following (though substantially more complex, most of the time): SELECT fooA.field1 as field1B, fooA.field2 as field1C FROM foo as fooA ...

SQL: Select * from

i hava a query of the following form select * from tablename where id= $var now sometimes i need to select a recod with a particular id ,and sometimes i need to select recore with any id so i intent to something of follwoing form $v = $_GET['id']; if($v== -1) $var = '*' //here i want to select all recods else $var = $v //sele...

PostgreSQL, Foreign Keys, Insert speed & Django

A few days ago, I ran into an unexpected performance problem with a pretty standard Django setup. For an upcoming feature, we have to regenerate a table hourly, containing about 100k rows of data, 9M on the disk, 10M indexes according to pgAdmin. The problem is that inserting them by whatever method literally takes ages, up to 3 minutes...

Group by and where?

I'm trying to calculate some columns in a mysql database with this code: "SELECT SUM(klick) FROM svar GROUP BY pollid HAVING pollID="& rstPoll("PollId") But it doesn't work. So what I want to do is to get the sum of all "klick" where a pollId has a certain value. I got this code to work with access but not with mysql: "SELECT SUM(kli...

Matching only one specific row in a JOIN where many exist

(Advantage Database Server) I have a table of service providers that, for auditing purposes, are never deleted. They have a start date and end date; in the case of changes like name or address, the existing row is end dated, a new row is created, and a new start date is assigned for the changed data. During processing of payments to tho...

ActiveRecord: Clever Join and Include

Hi all, I am trying to get ActiveRecord to perform the following query: SELECT A.*, B.*, C.* FROM A INNER JOIN B ON B.ID = B_ID INNER JOIN C ON C.ID = C_ID The dataset is rather large, and I need as the best performance, hence this specific query. I have my models and query as follows: class A < ActiveRecord::Base belongs_to :b ...

Is there any way to flush output from PL/SQL in Oracle?

I have an SQL script that is called from within a shell script and takes a long time to run. It currently contains dbms_output.put_line statements at various points. The output from these print statements appear in the log files, but only once the script has completed. Is there any way to ensure that the output appears in the log file a...

Use SELECT AVG with parameters "SELECT AVG(@parameter)" SQL!!

I'm working with a table of float values, but I enter the column by parameter SELECT AVG(@var) AS Aver FROM ListVal When I enter the column name (the parameter) I recieve an error: That the datatype nvarchar is not valid for the avg operator. But all the columns are float. Help!!! please ...

How do I compare 2 ints in sql?

I'm looking for a way to return a bit representing whether 2 ints are equal. When I try the following I get "Incorrect syntax near '='." What am I missing? I'm using SQL Server 2005. DECLARE @Table1Count AS INT DECLARE @Table2Count AS INT SELECT @Table1Count = COUNT(*) FROM Table1 SELECT @Table2Count = COUNT(*) FROM Table2 PRINT ...

SQL max and null

I am trying to get the null value in the USAGE_START_DATE column. So far, what is i got is the unique max records of those with value in the USAGE START DATE column, and could not find any the records with nulls in the USAGE START DATE. I already tried ISNULL, COALESCE, NVL, NZ. I got 2 tables: linked by Reservation_id. Reservation...

Comparing two T-SQL tables for diffs

I have two instances of the same database. The first db represents data from today, the second data from 6 months ago. I need to find differences for a subset of entries in a specific table. For entries with ids that are in both tables, I'd like to find a way to view only the rows that aren't identical. Any ideas? Thanks ...

How to pivot in SQL

I am not sure if this would be called pivoting. Data in my SQL 2005 table [CustromerRoles] is as such: CustId RoleId 2 4 2 3 3 4 4 1 4 2 [Roles] table: RoleId Role 1 Admin 2 Manager 3 Support 4 Assistant I want to create a view such that: SELECT * FROM [MYVIEW] will...

The SELECT permission was denied on the object 'sysobjects', database 'mssqlsystemresource', schema 'sys'.

SETUP: SQL Server 2005 & DotNetNuke 05.01.02. This started with me trying to install a DNN Module that had "select * from dbo.sysobjects" in it's SQL scripts. That failed with the following error: The SELECT permission was denied on the object 'sysobjects', database 'mssqlsystemresource', schema 'sys'. I logged into the data...

In MYSQL, how can I select multiple rows and have them returned in the order I specified?

Hi, I know I can select multiple rows like this: select * FROM table WHERE id in (1, 2, 3, 10, 100); And I get the results returned in order: 1, 2, 3, 10, 100 But, what if I need to have the results returned in a specific order? When I try this: select * FROM table WHERE id in (2, 100, 3, 1, 10); I still get the results returned...

How do I cast a ForeignKey constraint on a column to another table with multiple-column Primary Keys?

Question as per stated in the title. For example: Table_Groups: - name (pri key) - other_tables (ForeignKey to Tables) Tables - name (pri key) - color - country_of_make (pri key) ...