24

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 ...


12

You could use LATERAL, like in this query: SELECT DISTINCT x.n FROM atable 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 ...


12

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 FROM data GROUP BY dept, role1, role2 HAVING COUNT(distinct DEF) > 1 ) dup ON dup.dept = d.dept AND dup.role1 = d.role1 AND dup.role2 = d.role2 ; The subquery returns ...


10

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 b)|| array_agg(distinct c)|| array_agg(distinct d) ) from t order by 1; | unnest | | :----- | | 0 | | 1 | | 2 | | 3 ...


9

There are probably many ways to do this. The first that comes to mind is to use window functions: SELECT id, postcode FROM ( SELECT id, postcode, ROW_NUMBER() OVER (PARTITION BY id ORDER BY MAX(date_created) DESC ) AS rn FROM tablename GROUP BY id, postcode ) AS t WHERE ...


9

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 ...


9

To just update an arbitrary one from each distinct group you could use WITH T AS (SELECT ROW_NUMBER() OVER (PARTITION BY [Finance_Project_Number] ORDER BY (SELECT 0)) AS RN, [Processing_Result_Text], [Processing_Result] FROM [InterfaceInfor].[dbo].[ProjectMaster] ...


8

You could do something like: select distinct x.id, x.lastname, x.firstname, x.email from t as x join ( select id from t group by id having count(distinct email) > 1 ) as y on x.id = y.Id


8

Replace your dbname and schemaName in the following query. ;WITH CTE AS ( SELECT [Order_No] ,[Customer_Name] ,[Purchase_Cost] , ROW_NUMBER() OVER(PARTITION BY [customer Name] ORDER BY [Purchase Cost] DESC) AS "RowNumber" FROM [dbname].[schemaName].[PurchaseTable] ) SELECT TOP(3) [Order_No] ,[Customer_Name] ...


7

Shortest 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); Fastest With an index on each involved column! For few distinct / many ...


7

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. SELECT FPD1.[Food item ID] AS ItemID ,MAX(I1.[Invoice Date]) AS MaxDate FROM [Food purchase data] AS FPD1 INNER JOIN ...


7

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 $1||$2; $$; ...


6

You need conditional aggregation using sum(case): select cat_id, 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 from tab group by cat_id Edit: My crystal ball was apparently broken, but now it's fixed :-) If you ...


6

You can number and order id by us_state using the ROW_NUMBER() Window Function and only keep the n first values: SELECT * FROM ( SELECT * , ROW_NUMBER() OVER(PARTITION BY us_state ORDER BY id) as n FROM data ) as ord WHERE n <= 2 ORDER BY us_state ; Or you can CROSS JOIN with a subquery: SELECT l.* FROM ( SELECT DISTINCT us_state FROM data )...


6

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 ...


6

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); insert ...


6

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, ... FROM table_name GROUP BY col1, col2, ... ; Another way to look at how it works - probably more accurate - is that it acts as the common bare SELECT (...


6

This looks more like a stack overflow question yet Union will give a distinct combination. SELECT concat("from",':',"to") as res From Table UNION SELECT concat("to",':',"from") From Table


5

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, ...


5

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 ...


5

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) ...


5

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 FROM @CRV_AttributeLog WHERE AttributeID LIKE N'online_status' GROUP BY RoomId ) SELECT r.RoomName, a.AttributeId,...


5

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 ...


5

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'; -- Aa 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 ...


5

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. select colum1, count(*) as number_of_rows from your_table where extract(year from date) = 2014 group by column1;


5

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 ...


4

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 ...


4

I believe you are asking for the sum of "distinct" values of owgh for each value of mid and pid - a different "granularity" of data. e.g. for mid = 3 then add 1.5 + 0.6 + 1.2 + 3.0 = 6.3; only count the 1.5 once instead of twice. However if your record ids for the 1.5 were for 2 different pid values, then you would want to count the 1.5 twice. One way you ...


4

Your query will give all unique combinations of product-id and name in the table, so if for example product id 123 has name "Pencil" in English and "Crayon" in French, you will get: 123 | Pencil 123 | Crayon The results are deterministic in that all unique combinations that exist in your table, will be returned in that query. There isn't an option of which ...


4

What would be the most optimal plan for querying all distinct user_id for a significant part of the operation_id set (20%-60%). Use a recursive query: WITH RECURSIVE cte AS ( ( -- parentheses are required SELECT user_id FROM user_operations WHERE operation_id < 500 ORDER BY user_id LIMIT 1 ) UNION ALL SELECT u.user_id ...


Only top voted, non community-wiki answers of a minimum length are eligible