Update: Tested all 5 queries in SQLfiddle with 100K rows (and 2 separate cases, one with few (25) distinct values and another with lots (around 25K values).
A very simple query would be to use UNION DISTINCT. I think it would be most efficient if there is a separate index on each of the four columns It would be efficient with a separate index on each of the ...
Using standard SQL on most RDBMS, there are various ways.
Using a subquery:
SELECT d.dept, d.role1, d.role2, DEF
FROM data d
INNER JOIN (
SELECT dept, role1, role2
GROUP BY dept, role1, role2
HAVING COUNT(distinct DEF) > 1
ON dup.dept = d.dept AND dup.role1 = d.role1 AND dup.role2 = d.role2
The subquery returns ...
You could use LATERAL, like in this query:
CROSS JOIN LATERAL (
VALUES (a), (b), (c), (d)
) AS x (n)
The LATERAL keyword allows the right side of the join to reference objects from the left side. In this case, the right side is a VALUES constructor that builds a single-column subset out of the column values you ...
To be clear, I'd use union as ypercube suggests, but it is also possible with arrays:
select distinct unnest( array_agg(distinct a)||
array_agg(distinct d) )
order by 1;
| unnest |
| :----- |
| 0 |
| 1 |
| 2 |
| 3 ...
There are probably many ways to do this. The first that comes to mind is to use window functions:
( SELECT id, postcode,
ROW_NUMBER() OVER (PARTITION BY id
ORDER BY MAX(date_created) DESC
) AS rn
GROUP BY id, postcode
) AS t
I have exactly the same set up and I've been through the same stages of rewriting the query.
In my case the table names and meaning is a bit different, but overall structure is the same. Your table Transactions corresponds to my table PortalElevators below. It has ~2000 rows. Your table TxLog corresponds to my table PlaybackStats. It has ~150M rows. It has ...
To just update an arbitrary one from each distinct group you could use
AS (SELECT ROW_NUMBER() OVER (PARTITION BY [Finance_Project_Number]
ORDER BY (SELECT 0)) AS RN,
Replace your dbname and schemaName in the following query.
;WITH CTE AS
, ROW_NUMBER() OVER(PARTITION BY [customer Name] ORDER BY [Purchase Cost] DESC) AS "RowNumber"
Is it possible to create an aggregate function (SUM_DISTINCT), that returns the same result as as SUM(DISTINCT foo), so SUM_DISTINCT(foo) = SUM(DISTINCT foo)?
Yes, it is possible — you need a User-defined Aggregate, such as this:
create or replace function f_sum_distinct (numeric, numeric) returns numeric
language sql as $$
SELECT DISTINCT n FROM observations, unnest(ARRAY[a,b,c,d]) n;
A less verbose version of Andriy's idea is only slightly longer, but more elegant and faster.
For many distinct / few duplicate values:
SELECT DISTINCT n FROM observations, LATERAL (VALUES (a),(b),(c),(d)) t(n);
With an index on each involved column!
For few distinct / many ...
MS Access is rather limited.
I assume that it is possible to have more than one invoice for the same date.
In this case I'll pick an invoice with the highest ID.
At first we'll find maximum Invoice Date for each Food Item.
FPD1.[Food item ID] AS ItemID
,MAX(I1.[Invoice Date]) AS MaxDate
[Food purchase data] AS FPD1
INNER JOIN ...
How does this work exactly?
It gives you distinct combinations of all the expression in the SELECT list.
SELECT DISTINCT col1, col2, ...
FROM table_name ;
is also equivalent to:
SELECT col1, col2, ...
GROUP BY col1, col2, ... ;
Another way to look at how it works - probably more accurate - is that it acts as the common bare SELECT (...
You need conditional aggregation using sum(case):
sum(case when status = 1 then 1 else 0 end) as published, -- only count status 1
sum(case when status = 0 then 1 else 0 end) as unpublished,-- only count status 0
count(*) as totals
group by cat_id
My crystal ball was apparently broken, but now it's fixed :-)
If you ...
You can number and order id by us_state using the ROW_NUMBER() Window Function and only keep the n first values:
, ROW_NUMBER() OVER(PARTITION BY us_state ORDER BY id) as n
) as ord
WHERE n <= 2
ORDER BY us_state
Or you can CROSS JOIN with a subquery:
SELECT DISTINCT us_state FROM data
You need to GROUP BY instead of DISTINCT (the effect is the same) and you need to aggregate the column you want to use for sort order. In this case I used MIN, but you can use whatever makes sense here.
SELECT c.foo, c.bar
FROM Parent p
JOIN Child c on c.parentId = p.id
GROUP BY c.foo, c.bar
ORDER BY MIN(p.createdDate);
Please note that, since you're ...
What you have almost works, just remove the distinct and change the > 2 to > 1. The distinct is not necessary as the grouping handles that and the > 2 is looking for things that have at least three entries rather than just two.
drop table tab1;
create table tab1 as (select 1 column_fk_id1,2 column_fk_id2 from dual);
insert into tab1 values (1,2);
This is a classic greatest-n-per-group problem. They frequently arise in a whole host of areas and, like Analytic functions (see below) are well worth studying.
Nowadays, it is typically solved by using Analytic (aka Window) functions - see the fiddle here.
You can use this query -
WITH cte AS
ROW_NUMBER() OVER (PARTITION BY col1, col2 ...
You can still use DISTINCT ON. Just wrap it into an outer query to sort to your needs. See:
Get distinct on one column, order by another
PostgreSQL DISTINCT ON with different ORDER BY
SELECT DISTINCT ON (col1)
col1, col2, col3
ORDER BY col1, col3 DESC
ORDER BY col3 DESC, col2;
Assuming that col2 ...
Typically you would have another table (let's name it tbl) with all distinct id values in separate rows. If you don't, create it:
CREATE TABLE tbl AS
SELECT DISTINCT id FROM postcode ORDER BY id; -- ORDER is optional
Or replace tbl with in below query with the same SELECT as subquery, but that's (much) more expensive.
If there can be many rows per id, ...
You can count distinct elements by running:
select count(distinct policy_id, client_id) from policy_client;
Another option would be to group by and count that:
select count(*) from (select policy_id, client_id from policy_client group by 1,2) a;
Run both version and see which one performs better on your dataset.
A very quick way but not totally accurate ...
There are 3 possible kinds of duplicates:
Duplicates within the rows of the bulk insert.
Duplicates between inserted rows and existing rows.
Duplicates between inserted rows and concurrently inserted / updated rows from other transactions.
Just like I explained in this closely related answer:
Using EXCEPTION to ignore duplicates during bulk inserts
It seems like you really want a PIVOT.
First, we'll create your DeviceLicense table, and populate some sample data:
CREATE TABLE dbo.DeviceLicense
SINo int NOT NULL
, DeviceFilter nvarchar(50) NULL
, OrganizationID int NOT NULL
INSERT INTO dbo.DeviceLicense (SINo, DeviceFilter, OrganizationID)
VALUES (1, NULL, 1001)
, (2, NULL, 1001)
If there are more than one AnalogValue, you can get max value for each RoomId and then JOIN with CRV_AttributeLog just to fetch all attributes.
WITH maxTime as
SELECT RoomId, MAX(LogTimeStamp) AS LogTimeStamp
WHERE AttributeID LIKE N'online_status'
GROUP BY RoomId
No, they are not necessarily "treated the same by the database engine." A test below shows that you might get different query plans.
In many cases, the difference between query plans may not matter for you. But in some (likely rare) cases it could matter significantly. For example, if SQL Server has a very poor cardinality estimate for one branch of your ...
Good job investigating so far. Some initial notes:
I wouldn't worry about that function from the S.O. answer.
RTRIM and LTRIM only trim spaces, not white-space in general:
SELECT RTRIM('A ') + 'a';
SELECT RTRIM('A ' + CHAR(9)) + 'a'; -- CHAR(9) = tab
-- A a
Adding GROUP BY (2nd query) doesn't change that query since it was implied in ...
In this case you can use GROUP BY to get distinct column1 values, and instead of convert the date to text you can use EXTRACT function for this purpose.
count(*) as number_of_rows
extract(year from date) = 2014
SELECT COUNT (DISTINCT column_name) FROM table_name;
The part of the PostgreSQL code that implements "COUNT(DISTINCT...)" is quite old and hasn't had much performance work done on it recently. It can't take advantage of either parallel processing, or hash tables, for example. You could rewrite with a subquery to possibly take advantage of some newer ...
You need two changes:
a major one: Using an non-aggregated column in a GROUP BY query will yield unpredictable result - it's a pity that default setting s in MySQL allow this type of query, and good that it is corrected in 5.7 version.
The problem with your query is that an email address can appear in many rows - with many different created_at values - and ...