The set of all distinct rows from two or more result sets.

learn more… | top users | synonyms

-3
votes
1answer
46 views

How does Oracle handle types in this UNION query? [on hold]

SELECT 1 FROM DUAL UNION SELECT '2' FROM DUAL; what does this give? knowing dual is default table and is a single row table.
7
votes
2answers
96 views

Most efficient way to generate a diff

I have a table in SQL server that looks like this: Id |Version |Name |date |fieldA |fieldB ..|fieldZ 1 |1 |Foo |20120101|23 | ..|25334123 2 |2 |Foo ...
0
votes
2answers
50 views

How many passes of a table are done with UNION ALL? [closed]

I am trying to run a single query that will make a single pass through a table, and was wondering if by using UNION ALL to make counts of total row unioned with counts of unique rows so I would be ...
2
votes
1answer
58 views

Selecting max(date) from union query

I am having a bit of trouble getting accurate results from a query that looks to me as if it should work. RDBMS is MySQL. I am trying to get the max last login date from a union select on three ...
0
votes
1answer
41 views

Does PostgreSQL benefit from more cores or faster cores for READ heavy applications? [closed]

I have been trying to figure out how to go about configuration of a PostgreSQL instance that will have 64GB ram (which is far larger than the database) and if I should opt for more cores or faster ...
1
vote
1answer
25 views

To find out the sum total of GRNQty and GRNValue of particular item from column 1

Query 1 # SELECT spi.Item,spi.ItemName,supdd.PackageID,spi.Specifics,iteb.brandname,supdd.Qty AS GRNQty,supdd.GP AS UnitPrice,(supdd.Qty * Supdd.GP) AS GRNValue FROM suppdeldetail AS supdd ...
3
votes
1answer
180 views

How to union four different tables

I have two tables whereby both of them use sub queries. These two tables are combined by using UNION ALL. I have two other tables that only uses a WHERE clause and both of these are combined using ...
0
votes
1answer
107 views

Optimizing key lookup in subquery with UNION ALL

I am trying to optimize following query (little bit more complex actually, but this is important part): SELECT Id, StatusDate, [...Lot Of Columns...] FROM ( ( SELECT Id, StatusDate, [...Lot ...
1
vote
0answers
44 views

How to improve performance when joining against coalesce

In an effort to remove a caching layer from our application, I'm building a number of views to encapsulate the different ways a user can have access to a piece of content - one per "rule" of access. ...
1
vote
1answer
63 views

MySQL Summary with Union

The following query does what I want, but I need to extend it, but i'm just not enough of an SQL expert to figure out what I'm doing wrong. SELECT sd.StockNumber AS StkNm, sd.Description As Descr, ...
1
vote
2answers
93 views

Combine results of two queries, where the second relies on the first

I have one query which is returning name(text), total, created_at And another query which is returning the same thing name(text), total, created_at The second query usually is returning more ...
0
votes
0answers
42 views

Footer with counter of records, using union of query

I have a report that has a header (display the current date), I use UNION to join the header with the main query that returns multiple records, it has 10 columns. After that another UNION for joining ...
0
votes
1answer
71 views

Grouping UNION ALL

No matter how I try to group the record set generated by this query it just wont do it. Query works ok the problem is trying to Group by A SELECT CountryCode + + ProductNumber + + StockType AS A, ...
0
votes
0answers
62 views

Sum of nested group query

I'm very new to SQL, so please bear suggest improvements anywhere. I have a PaymentsReceived table like this (simplified to only what is relevant) AccountID refrerences Accounts(ID) PaymentDate ...
2
votes
2answers
61 views

How can I simplify this UNION?

Here's the case - I have the following query: SELECT DISTINCT post_id FROM ( ( SELECT DISTINCT rp.post_id, pm.meta_value AS premiere_date FROM wp_related_people AS rp JOIN ...
1
vote
3answers
880 views

Select only rows with minimum values from union table

SQL Server 2005 Here is a sampling of the data from a union of two tables (named "vProducts_All"): DBID UPC Price1 Price2 1 023614025559 689.0000 649.0000 2 ...
1
vote
1answer
128 views

Refactoring sql for tables with similar columns but different table names

I have the task of re-factoring a SQL script that performs a lot of unions from different tables. These tables have similar information, so the script queries the same columns as in each table, except ...
0
votes
0answers
265 views

Performance of VIEWs with UNIONs in different databases

For various corporate and historical and replicating reasons, I have multiple databases that contain similar-structured tables, holding the main production data used as a source for other home-made ...
1
vote
2answers
122 views

WITH Clause UNION Issue

WITH [ProgressInvoicesPeriodCost] AS ( SELECT ProgressInvoices.ProjectId , ProgressInvoices.PeriodId , SUM(ProgressInvoiceDetails.TotalThisInvoice) AS TotalThisInvoice ...
2
votes
1answer
72 views

Best way of finding rows referencing a given id on PostgreSQL

I'm trying to find if there would be anything violating the foreign key constraint when deleting a row, to check if I can delete it or not (can't use ON DELETE CASCADE) For that, I have a piece of ...
2
votes
1answer
158 views

How to use COLLATE with UNION

How to use COLLATE with UNION? I want to union 2 tables (both have the same columns and the same types: varchar,int, int, decimal). I get error: sg 468, Level 16, State 9, Line 1 Cannot resolve ...
3
votes
4answers
665 views

Can I combine the results from multiple columns into a single column without UNION?

I have a table with several columns which I want to SELECT: SELECT his_name , her_name, other_name FROM foo; Bu, I instead I want to combine the results all into a single column. As an example, I ...
1
vote
0answers
18 views

Is there a faster way to get UNION ALL behavior in Postgres? [duplicate]

I have a table (t0), in my Postgres DB, with data that looks something like this: t1_id t2_id 1 1 2 1 2 1 4 null 4 null 5 null And I have a query to return my ...
1
vote
2answers
662 views

Is there a faster way to get UNION ALL behavior in Postgres?

I have a table (t0), in my Postgres DB, with data that looks something like this: t1_id t2_id 1 1 2 1 2 1 4 null 4 null 5 null And I have a query to return my ...
0
votes
2answers
158 views

Calculating average with AVG for a list of numbers

This is probably a newbie question (bear with me) but how can I calculate the average for a list of numbers using SQL and the AVG function? I mean, I can add together numbers in SQL like: SELECT ...
2
votes
2answers
2k views

Get table name from union query?

This is my query SELECT Id, productName, Largeimagepath, Discount, Price, Image FROM tablename1 where Active =1 union SELECT Id, productName, Largeimagepath, Discount, Price, Image FROM tablename2 ...
0
votes
1answer
174 views

How to union tables into a new table in mysql? [closed]

I have a table as following: id col1 col2 col3 type 1 12 12 23 1 2 23 33 1 0 3 333 2 21 1 ..... I try to select 1% sample of where type=1 and all the data where type=0, I ...
0
votes
0answers
24 views

Combining 2queries: One filtered Users on Role, One Select users with only one specific role

First query: Selects All users with specific rid ( 3 or 4 or 19 or ...). SELECT u.uid AS uid, u.name AS name, fn.field_full_name_value AS field_full_name_value FROM users u LEFT OUTER ...
1
vote
2answers
77 views

Merge output of two Queries MYSQL

I have a mysql table with the following schema: +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
0
votes
0answers
58 views

Optimization of an old query multiple unioned subqueries

I am currently in the process of optimizing a large query that was written for oracle 8i. My company currently uses oracle 10g which gives us a lot of options for optimization. Please Reference the ...
0
votes
1answer
2k views

The best way to query a partitioned table in Oracle

I have a table in an Oracle database, partitioned by a field. In our application we need to query data that is scatered across 2 partitions. Can you please tell me which method is better: making ...
0
votes
1answer
49 views

whether to merge partitioned table with only partially overlapping columns

I opted for MonetDB for my static data, but this might be less of a MonetDB-specific question about features than about RDBMS (or just SQL) in general. I have data with a few "cross-sectional" ...
0
votes
1answer
455 views

Query improvements without UNION

I have the following scenario. A users table, a records table and a memberships table. A user can be associated to zero or more records either directly with the records table records.user_id = ...
1
vote
1answer
69 views

How to perform almost the same query N times without rewriting it N times?

Basically I have a list of numbers like: 1,2,3,4,5 And I want to perform a query like this for each of the previous numbers: SELECT COUNT(*), field_name FROM my_table WHERE field_name = NUMBER ...
0
votes
1answer
85 views

Ask about Inner Join & Union

i want to ask about my script this is doesn't work. i need to help ! SELECT A.*,B.mat_nama,C.mats_nama FROM pengeluaran AS A INNER JOIN(material AS B) ON(A.mat_id = B.mat_id) INNER JOIN(mat_satuan ...
1
vote
0answers
52 views

Does union all have an enforced ordering

I know that a union does a de-duplication so can jumble the results, but I can't find a definitive answer for union all Example query select id from ( select id from table where id = 1 union all ...
-1
votes
1answer
475 views

Group by for UNION not working Postgresql

I can't find a way to make Group by works in Postgresql, How can i make group by works so each name will be only one , not repeated many times like what i got in this picture? Here is a code for what ...
-2
votes
1answer
118 views

how to count result set for select statment [closed]

I have to make query from DB. I found some solution but it isn't so precise as it need to be. Here result of my query: country weight Germany 1687.5 Gt.Britain 1687.5 Japan ...
0
votes
1answer
165 views

Combine results from two queries?

I need to combine the results from two queries into one result set and order them altogether. This is the working, raw SQL code I have: SELECT * FROM ( SELECT t1.id AS entity_id, ...
2
votes
1answer
1k views

Postgres: “Pivot” unioned table based on one column

I have a query that does this sort of thing: (SELECT rel_id, timestmap, y FROM table_1 AS full_ WHERE full_.timestamp BETWEEN %s AND %s ORDER BY full_.timestamp) UNION ALL (SELECT rel_id, ...
1
vote
2answers
2k views

Find nearest values from MySQL Table

I want to fetch nearest rates for a given value from below table. mysql> select * from rates; +------+----------+----------+ | ID | Type | Rate | +------+---------------------+ | 1 | ...
0
votes
1answer
59 views

Optimizing a union query

SELECT id FROM user WHERE id IN ( 2,3,4,7,9,21,23,45,324,12,43,254 ) UNION SELECT DISTINCT user_id FROM workgroup_users WHERE workgroup_id IN ( 1,345,34,654,213,123,5,2,3,4 ) I know we should not ...
1
vote
1answer
775 views

Fast query, in theory using index, still in the slow-query log

I'm having a hard time interpreting the EXPLAIN results of these queries. They both end up in the slow-query log, but the execution time is ~0.0050ms and the final result set is always under 100 rows. ...
3
votes
2answers
2k views

Postgres 9.2 select multiple specific rows in one query

I have a table with three columns, Id Serial. Value real. timein Timestamp. I want to select the values based on a range of days ie. from two days ago until now. The table may contain one or two ...
2
votes
1answer
1k views

Best practice: Unions or a derived table?

I've inherited a medium-sized database with a terrible schema. The sanitized portion in question is like so: CREATE TABLE `pending` ( ... `invoice` int(11) DEFAULT NULL, `lid` int(11) DEFAULT ...
6
votes
3answers
2k views

Optimize UNION query in MySQL

I have a problem with a UNION query in MySQL. We have 10 millions players on our website and we would like to select players with a multi-criterias system. For exemple, selecting US people, men, more ...
0
votes
1answer
675 views

Mysql: order by field or select union?

As a newbie, I expected Mysql to return the rows of this query ordered by their ids order in the id clause select * from test where id in (3,1,2) Unfortunatelly for me, that's not true. The results ...
1
vote
1answer
83 views

Merge multiple “is exists” queries into one statement

I'm trying to figure out how to check if multiple rows exist in multiple tables in one statement. Given the following tables: table table_one ======== id 'a' 'b' 'c' table table_two ======== ...
3
votes
1answer
801 views

Unable to run union in parallel on SQL Server

I am unable to get this fairly simple query to parallelize the union operation: select va.ObjectId, 0 as IsFlag from Oav.ValueArray va where va.PropertyId = @pPropertyId ...
1
vote
2answers
143 views

How to Perform Two Simple Queries using Select Value from First Query

I need to perform a select operation and get the id column's value (single value returned). Using this value, I need to perform two more selects on the same table using UNION ALL (in order to select ...