New answers tagged select
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
