New answers tagged

1

You can accomplish this by using a dedicated numbers_table. This is a table with a single column number and can have as many values as you require (I have 0 to 999) For your question, there are two scenarios: The first is if you do not know the keys, or they change. The second is if you do know the keys and in this case you can get each key of the json ...


0

Without using a table, get date on the fly without using cursor. DECLARE @SDate DATE = '20140101', @EDate DATE = '20140106'; SELECT TOP (DATEDIFF(DAY, @SDate, @EDate) + 1) ResultDate = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate) FROM sys.all_objects a CROSS JOIN sys.all_objects b;


0

JSON is always tricky and you have to search a lot of tutorials to get the hang of it. But you can use normalized table instead of JSON. CREATE TABLE tab ( `id` INTEGER, `name` JSON ); INSERT INTO tab (`id`, `name`) VALUES ('1', '{"vals":[{"id":123,"name":""}],"additional":"text"}'), ('2'...


0

SELECT * FROM table t1 WHERE NOT EXISTS ( SELECT NULL FROM TABLE t2 WHERE t2.`value` = -t1.`value` AND t2.`date` BETWEEN t1.`date` - INTERVAL 7 DAY AND t1.`date` + INTERVAL 7 DAY )


11

The query returns a float data type, which is a binary structure that has no comma or dot decimal separator. It is the rendering application that converts the value into a string format for display purposes, which can honor the client's regional settings if programmed to do so. Although you could change the T-SQL to return a formatted string like the example ...


1

The query can be next: SELECT -- here we concatenate articles by tags count GROUP_CONCAT(ArticleID ORDER BY cnt DESC) FROM ( -- here we calculate different tags cout per article SELECT ArticleID, COUNT(DISTINCT Tag) cnt FROM TagMap WHERE Tag IN('Tag1', 'Tag2', 'Tag3') GROUP BY ArticleID ) t; Working example here: PHPize.online


1

This would helpful, Give a try once SELECT GROUP_CONCAT(ArticleID) ,COUNT(1) AS CNT FROM (SELECT Tag ,ArticleID ,COUNT(1) AS CNT FROM TagMap Tag IN (...) GROUP BY Tag ,ArticleID) tmp GROUP BY Tag ORDER BY cnt


4

Without the DISTINCT, the type of the second part of the UNION ALL is unknown: SELECT pg_typeof(NULL); pg_typeof ----------- unknown (1 row) This gets resolved to integer. If there is a DISTINCT, PostgreSQL has to resolve the type of NULL earlier on: to eliminate duplicates, it has to know which equality operator to use. Lacking other information, it ...


Top 50 recent answers are included