derived-table

Unfamiliar character in SQL statement

This is sort of SQL newbie question, I think, but here goes. I have a SQL Query (SQL Server 2005) that I've put together based on an example user-defined function: SELECT CASEID, GetNoteText(CASEID) FROM ( SELECT CASEID FROM ATTACHMENTS GROUP BY CASEID ) i GO the UDF works great (i...

How to combine variable assignment with data-retrieval operations in T-SQL

Just to clarify, I'm running Sybase 12.5.3, but I am lead to believe that this holds true for SQL Server 2005 too. Basically, I'm trying to write a query that looks a little like this, I've simplified it as much as possible to highlight the problem: DECLARE @a int, @b int, @c int SELECT @a = huzzah.a ,@b = huzzah.b ,@c = ...

SQL Server Performance: derived table vs. common table expression (CTE)

Is there any performance gain using a CTE over a derived table? ...

indexes in mysql SELECT AS or using Views

I'm in over my head with a big mysql query (mysql 5.0), and i'm hoping somebody here can help. Earlier I asked how to get distinct values from a joined query http://stackoverflow.com/questions/508707/mysql-count-only-for-distinct-values-in-joined-query The response I got worked (using a subquery with join as) select * from media m i...

SQL Help in Access – Looking for the Absence of Data

I am trying to find the fastest way to find all of the records in a parent table that do not have certain records in a child table. For example, I want the query to return all of the family records where there are no male children or no children at all. Example 1 This is painfully slow: SELECT * FROM Families WHERE Families.FamilyID ...

Linq to Sql, derived to derived association

I dont know if its possibe. I have a base table with 2 derived tables. Mammal Mammal -> Boy Mammal -> Dog A want that Dog table has a foreign key(ownerBoyId) to Boy table, but linq designer dont permit, because Boy itself dont have a Id collumn, Id is inherited. How can I resolve this problem? Associate to Base class? ...

How can I further optimize a derived table query which performs better than the JOINed equivalent?

UPDATE: I found a solution. See my Answer below. My Question How can I optimize this query to minimize my downtime? I need to update over 50 schemas with the number of tickets ranging from 100,000 to 2 million. Is it advisable to attempt to set all fields in tickets_extra at the same time? I feel that there is a solution here that I'm ...

indexes and speeding up 'derived' queries

I've recently noticed that a query I have is running quite slowly, at almost 1 second per query. The query looks like this SELECT eventdate.id, eventdate.eid, eventdate.date, eventdate .time, eventdate.title, eventdate.address, eventdate.rank, event date.city, eventdate.state, eventdate.name, source.link, type, eventdate.img FROM s...

I wish I could correlate an "inline view"...

I have a Patient table: PatientId Admitted --------- --------------- 1 d/m/yy hh:mm:ss 2 d/m/yy hh:mm:ss 3 d/m/yy hh:mm:ss I have a PatientMeasurement table (0 to many): PatientId MeasurementId Recorded Value --------- ------------- --------------- ----- 1 A d/h/yy hh:mm:ss 100 1 A d/h/yy hh:mm:ss 200 1 ...

NHibernate + join to derived table

In a table that stores multiple rows per employee, I want to pull one row per employee that represents the most recent entry for each employee. Here's where I am with hand-written SQL: SELECT [all the selected columns here] FROM Nominations t inner join (select max(NominationId) mostRecentNominationId, EmployeeId from Nomi...

Optimizing a MySQL query with a large IN() clause or join on derived table

Let's say I need to query the associates of a corporation. I have a table, "transactions", which contains data on every transaction made. CREATE TABLE `transactions` ( `transactionID` int(11) unsigned NOT NULL, `orderID` int(11) unsigned NOT NULL, `customerID` int(11) unsigned NOT NULL, `employeeID` int(11) unsigned NOT NULL, ...

SQL Server: join on derived table that contains WITH clause?

I'd like to join on a subquery / derived table that contains a WITH clause (the WITH clause is necessary to filter on ROW_NUMBER() = 1). In Teradata something similar would work fine, but Teradata uses QUALIFY ROW_NUMBER() = 1 instead of a WITH clause. Here is my attempt at this join: -- want to join row with max StartDate on JobModel...

When is it not appropriate to use Derived tables?

This SO post details some benefits in performance regarding Derived vs. Temporary tables. Other than performance, what situations exist for which a Derived table would not be appropriate. One answer per post with an example would be helpful. ...

can't merge a union all view

I know Oracle RDMS can't merge a view that has a set operator in it. I want to know why is that. For example, this: SELECT u.* FROM ( SELECT a.a1 A, a.a2 B FROM tab_a a UNION ALL SELECT b.b1 A, b.b2 B FROM tab_b b ) u, tab_p p WHERE p.a = u.a could be transformed into this: SELECT * FROM ...

SELECT INTO USING UNION QUERY

Hi, I want to create a new table in SQL Server with the following query. I am unable to understand why this query doesn't work. Query1: Works SELECT * FROM TABLE1 UNION SELECT * FROM TABLE2 Query2: Does not Work. Error: Msg 170, Level 15, State 1, Line 7 Line 7: Incorrect syntax near ')'. SELECT * INTO [NEW_TABLE] FROM ( SELECT *...