Tag Info

Hot answers tagged

12

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


10

This was posted to pgsql-hackers mailing list and I tried to answer in brief there. It seems if the target list (specified columns) matches the tuple descriptor of the relation exactly, that is, both in number of columns and order, then the underlying scan can return a tuple that's directly consumable by the enclosing Sort node. On the other hand, if the ...


8

Shouldn't really need any hacks to handle leap years, but it depends on what results you expect. Typically you just subtract the larger component first, so subtract a month before you subtract a day, instead of the other way around. Do any of these produce results you don't expect? If so, which ones are "wrong" to you, and why? DECLARE @d TABLE(d DATE); ...


8

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


7

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; SQLFiddle here


6

Yesterday: SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE() - 1), 0) Today of Last Year: SELECT DATEADD(YEAR, -1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) Today of Last Month: SELECT DATEADD(MONTH, -1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) Yesterday of Last Year: SELECT DATEADD(YEAR, -1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE() - 1), 0)) ...


4

Late to the party and many good songs have already been played. But I bring some more good ones: Shortest SELECT DISTINCT n FROM observations, unnest(ARRAY[a,b,c,d]) n; However, a less verbose version of Andriy's idea is not much longer, but more elegant and faster. For many distinct / few duplicate values: SELECT DISTINCT n FROM observations, LATERAL ...


3

Firstly, find out your constraint name thus: select TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where REFERENCED_TABLE_NAME = 'My_Table'; And then remove the constraint by the following means: ALTER TABLE My_Table DROP FOREIGN KEY My_Table_Constraint; Refs: 1 & 2. ...


3

Since it seems clear that a statistic already exists for the particular column, I offer two possibilities: Object Explorer is pointed to a different database - I'm suspicious because the database has DEV in the name, so it's possible that in one case you're looking at dev and in another you're not. The plan being used is from before the statistic was ...


2

Assuming a JSON array in Postgres 9.4 jsonb column, this would do the job: SELECT DISTINCT ON (doc->'s') doc FROM ( SELECT '[ { "id":1, "s":"foo" }, { "id":2, "s":"bar" }, { "id":3, "s":"foo" }]'::jsonb AS j ) t , jsonb_array_elements(t.j) WITH ORDINALITY t1(doc, rn) ORDER BY ...


1

'true' != 'true' -> false 'false' != 'true' -> true NULL != 'true' -> NULL (and equivalent to FALSE in WHERE because it is not TRUE) What you can do is use COALESCE to change null into 'false' SELECT * FROM Table WHERE COALESCE(Bool, 'false') != 'true';


1

You can, but as I wrote and tested the function I felt wrong. It is a resources waste. Just please use a union and more select. Only advantage (if it is), one single scan from main table. In sql fiddle you need to change separator from $ to something else, like / CREATE TABLE observations ( id serial , a int not null , b int not null , c ...


1

Your data follows a strict pattern: If there is more than one entry like #TS%, then the first entry will be the one with #TS, the second will be the one with #TS - W. Obviously, if you sort descending, the order will be reversed. Therefore the solution seems obvious: group by #TS, get those lines where row_number >1, when sorted descending. SELECT * , ...


1

It looks like you're joining returns on the date and the type of item only, creating a semi-cartesian product. E.g. If you have 100 orders for item A, and 100 returns of item A, on the same day, you'll return 100 x 100 = 10,000 rows, with every return joined to every order. Is this what you want? In terms of performance, the difference between your two ...


1

(SELECT CASE DoYouWishToP When 0 Then 'Yes, urgently' When 1 Then 'Not peeing' END) is a Sub-select of the main select. It's simply a rule of SQL that it should be surrounded by parenthesis. Ypercube is also right: if the columns, like "DoYouWishToP", are from the same table, you don't need the second and third SELECT. In that case it will work without ...


1

So this query isn't going to be using an index, so will do a full table scan Yes it will definitely do table scan if there is no index. Will this table then be kept in the buffer cache after this query is executed? Or will it continually read from disk? No whole table might or might not be kept into buffer cache depending on size and whether SQL ...


1

Are strings in table columns represented as bit patterns or Unicode? Computers only deal with 1's and 0's (i.e. binary); datatypes indicate how to interpret that info. Would the queries perform faster if the column in the table was designed to have the year at the left of the name in the strings that are stored? (assuming the year component of the ...


1

If you are simply interested in adding a column Users_ID to the Content table, then the following will likely do the trick. (Assuming this is indeed SQL Server - see the 2nd code block for the MySQL version). /* add the Users_ID column to the table */ ALTER TABLE dbo.Content ADD Users_ID INT NULL CONSTRAINT FK_Content_Users_ID FOREIGN KEY ...



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