SELECT DATE_TRUNC('month', arrive_date) AS grouped_date, LOWER(arrive_location) AS location, x.key::int thing_type, sum(x.value::int) total FROM preprocess_things, jsonb_each(data) x GROUP BY grouped_date, location, x.key::int order by grouped_date, location, x.key::int; see https://dbfiddle.uk/?rdbms=postgres_13&fiddle=30dcd885d5581ab83c8f23602cba4b9c

The group by goes after the where clause. I believe you're looking for something like this. SELECT river.name, AVG(river_changes.size) AS AverageSize FROM river INNER JOIN hydro_river ON hydro_river.id_river = river.id_river INNER JOIN river_changes ON river_changes.id_hydro = hydro_river.id_hydro GROUP BY river.name; This is the output. name AverageSize one 278 three 60 two 11 You can find the fiddle https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=3f404da703cc676c6743b7a691001096 .

I have three tables, and I want to fetch the three table data without a foreign key. I think you mean "without a join condition". That's a Bad Idea. If you don't tell the database how to join two tables, it will perform a Cartesian Join, i.e. it will match every row in the first table against every row in the second table. If you have ten rows in both tables, you will get back a resultset of one hundred (10 x 10) rows. If you have a million rows in both tables, you will get back a resultset of one trillion (10^6 x 10^6) rows - assuming your database survives long enough to deliver it and you're prepared to wait that long! And the more tables you add, the more this multiplies out!

Edit the view criteria to be: WHERE ... (previous criteria) AND (CURRENT_USER() != 'bob@%' OR CURTIME() BETWEEN '22:00' AND '23:00') This restricts the bob user on these times, otherwise no results are returned in the view.

SUM and GROUP If I understood well what you want to know is the total UNITS of each product (not money) each customer has bought in all their orders COMBINED. If that is so, in your SELECT clause you should SUM(l.CANTIDAD) rather than COUNT() it, as you want to know the number of products, not the number of lines. The reason your results are only consistent with individual orders rather than all of them as a whole is because the orders table 'PEDIDOS' should be present in the GROUP BY clause as well: SELECT c.CODIGO, pr.NOMBRE, SUM(l.CANTIDAD) AS TOTALPEDIDO FROM CLIENTES c, PEDIDOS p, LINEAS l, PRODUCTOS pr WHERE p.CLIENTE = c.CODIGO AND l.NUM_PEDIDO = p.NUM AND pr.CODIGO = l.PRODUCTO GROUP BY c.CODIGO, p.NUM, pr.NOMBRE ORDER BY c.CODIGO;

I don't totally get the purpose of it, but from checking at your query, it looks like you're moving Address, Salary and PromotionDate to different rows, and then you're merging all together. One option is to avoid that expansion and later merge by doing all together in the same operation: SELECT a.AccountNo ,a.RunKey ,a.RunDate ,Item = STUFF((SELECT ',' + 'Address,Salary,PromotionDate' FROM @YourTable b WHERE a.AccountNo = b.AccountNo AND a.RunKey = b.RunKey AND a.RunDate = b.RunDate FOR XML PATH('')), 1, 1, '') ,Value = STUFF((SELECT ',' + b.Address + ',' + CAST(b.Salary AS VARCHAR(10)) + ',' + CAST(b.PromotionDate AS VARCHAR(10)) FROM @YourTable b WHERE a.AccountNo = b.AccountNo AND a.RunKey = b.RunKey AND a.RunDate = b.RunDate FOR XML PATH('')), 1, 1, '') FROM @YourTable AS a GROUP BY a.AccountNo ,a.RunKey ,a.RunDate Or even easier if you're using SQL 2017 or later (there might be some issues with this version though, if the output of the Item or Value columns is too long): SELECT a.AccountNo ,a.RunKey ,a.RunDate ,Item = STRING_AGG('Address,Salary,PromotionDate', ',') ,Value = STRING_AGG(a.Address + ',' + CAST(a.Salary AS VARCHAR(10)) + ',' + CAST(a.PromotionDate AS VARCHAR(10)), ',') FROM @YourTable AS a GROUP BY a.AccountNo ,a.RunKey ,a.RunDate Also, make sure there's a clustered index on AccountNo, RunKey, RunDate or at least one covering all the columns included in the query.

What you discovered is collectively called Pessimistic Locking or Mutual Exclusion (mutex). For it to work we need to choose an object to lock on. It's possible to lock on some record in DB (select ... for update) or you can ask PostgreSQL to create such object outside of tables (pg_advisory_xact_lock()). While pg_advisory_xact_lock() is a valid solution it's specific to PostgreSQL - and therefore it should be considered as the last resort. First solution that you should think of is finding records that we can lock on inside of existing tables. E.g. in your case it seems like you only append data and therefore you could lock on the oldest record with select ... for update. If this isn't practical (e.g. because it's possible that the table is completely empty), then yes - pg_advisory_xact_lock() would be a good choice.

Find id+filename which have passwords Find id+filename which have usernames Inner join with the original table to filter out those rows that have no corresponding values in passwordss and usernames Something along these lines: with passwords as ( select filename, id from files where name like '%PASS%' group by filename, id ), usernames as ( select filename, id from files where name like '%USER%' group by filename, id ) select files.* from files inner join passwords using(filename, id) inner join usernames using(filename, id);

To get from your example data to the desired result, all you need to do is group by firstName, lastName and take the max effDate: select firstName ,lastName ,max(effDate) effDate from table group by firstName ,lastName If you want to grab more information from the rows that contain the most recent effDate you can use an analytic function to provide an ordered column that can be used for filtering: select firstName ,lastName ,effDate from ( select firstName ,lastName ,effDate ,row_number() over (partition by firstName, lastName order by effDate desc) rn from table ) sq where rn = 1 (assuming effDate can't be null)

A couple of good points in the comments regarding being careful with premature optimization, and things to consider comparing such as the results themselves and the execution plan of each query. But to give some suggestions on what I normally do when the situation calls for a performance analysis, these are the steps in a loose order I follow: Compare the actual runtimes of each query, both the initial cold run, and then a few hot runs after. At the end of the day, time is generally the reason one wants to tune a query for performance, and if one instance of a query is significantly and consistently faster than another, then a lot of times that can be the clear answer. (Assuming all results are equal between the queries.) Compare the execution plans of each query. As Erik points out, you may find key differences between execution plans for one instance of a query vs another. Things that can be immediate performance killers which are visible via the execution plan are: cardinality estimate issues, inefficient join operators (e.g. Nested Loops being used on a large unordered dataset where a Hash Join operation would be better), under or over requesting of Memory resources, inefficient index operators (e.g. an index scan when an index seek may be more appropriate), etc. Compare the Time statistics. If the queries have been equal with all things considered so far, the Time statistics is a helpful resource. While the actual runtimes of each query may be similar, the CPU Times may differ, quite possibly significantly. In the case where the CPU Time was slower, that's more consumption of your server's provisioned resources that could've potentially been used elsewhere to serve other queries and processes. You actually see an example of this in your first query vs your third query, where the Elapsed Time of the first query is only 30% slower but CPU Time is over 300% slower. (Though in actuality, it's only a difference of a few milliseconds, so in this case probably not a concern.) Compare I/O statistics. Most times I go straight to the Logical Reads metric, which is a measurement of how many 8 KB data pages did the query cause SQL Server to load from disk. Similarly to the reasonings I mentioned in the Time statistics, if a query can result in less data pages being loaded off disk and searched to fulfill the data being served for the query, that's less resources needed in terms of I/O and Memory (to load the data from the pages into) which is a more efficient solution. Even if the queries have the same exact runtime, I'd typically prefer the solution that took less work from my server to get to the finish line.

You'll want to use a window function, specifically the https://mariadb.com/kb/en/lead/ function would make great use here like so: -- Gets the following row's search text and compare it to the current row's search text to see if the following row starts with the current row's WITH _SearchesMarkedAsRedundant ( SELECT id, userId, searchText, IF(LEAD(searchText) OVER (PARTITION BY userId ORDER BY id) LIKE CONCAT(searchText, '%'), 1, 0) ​IsRedundantSearch FROM Searches ) -- Returns only the rows whose following row did not start with this row's search text SELECT id, userId, searchText FROM _SearchesMarkedAsRedundant WHERE IsRedundantSearch = 0 And if you wanted to globally remove redundant searches, despite which user inputted the search, you can remove the PARTITION BY clause of the window function like so IF(LEAD(searchText) OVER (userId ORDER BY id) LIKE CONCAT(searchText, '%'), 1, 0) ​IsRedundantSearch. The above will accomplish the output from your given example. Note it specifically only looks at the next 1 row (sorted by the id column) and checks if the that row starts with the same search text as the current row. You can make it do a full contains check instead by adding another wildcard % symbol to the front of the searchText in the CONCAT() operator like so CONCAT('%', searchText, '%'). The LEAD() window function is the key to this solution here, as it can look-ahead at the column inputted by however many rows you specify. The default number of rows it looks-ahead is 1 but if you wanted to look at the 3rd row ahead instead of the next row, then LEAD(searchText, 3) would look-ahead by 3 rows.

No index can help with this data as it stands. The value you are looking for in embedded somewhere inside the field, not at the start of a field (where an Index could help). Because the database doesn't know what's at the start of the field, it can't navigate to the right bits of the index to get to the records you want. Reversing the value and indexing those values would help where the value is at the end of the field, but still won't help where the value is stuck in the middle, somewhere. To me, this screams of having "too much" data in this field. You're using string manipulation functions to extract part of the value, which says to me that the value you actually want is "wrapped" in "noise". Getting rid of that noise might be a far better option. select * from Folders ; +----+--------+------+ | id | parent | path | +----+--------+------+ | 1 | NULL | abc | The folder /abc | 2 | 1 | def | /abc/def | 3 | 2 | ghi | /abc/def/ghi +----+--------+------+ Much easier to traverse this structure recursively. Also, consider removing the distinct. All too often, I see distinct used as a "sticking plaster" to cover up an underlying problem with the data structures themselves or with a query, for example "duplicate" rows that appear in the results (usually caused by bad Joins) but "using distinct makes them go away". Getting rid of those "duplicates" can be a huge drain on the database ("select distinct a,b,c,d" can perform as badly as "group by a,b,c,d order by a,b,c,d"), so it's better to fix the root cause.

I personally see idea A much more complete, with some adjustments. – should the query be cleared as soon as they make a selection? – The problem I see in this design is that the query and the chips share the same field and the result is very confusing for the user. The logic would be a text editing field and the chip's area. The text inside a chip is not editable, while the query is. There is a possibility that the user wants to add one more character to those already written, I would keep the query but in a separate field and the chips in a clearly non-editable area. – Once the user selects a category, since they must only select one, should other Categories not be shown anymore – Pretty much EVERY TIME I have to select a checkbox, I always select the wrong one the first time. The only way to not start the search process over again in the event of an error is to keep all possibilities clearly visible. Ultimately, it would be useful to continue showing all possible categories and hide the subcategories of the unselected ones. – Once the user selects a category, should the rest of its child Subcategories appear automatically – I would let the user decide if he/she wants to see all the subcategories with a View all option. At this point, I think the most important action is the immediate search with the typed characters than displaying all the subcategories of an item. If there are three subcategories, it would not be a problem, but if there are more than ten, I would let the user choose if they want to momentarily abandon the search and use vertical scrolling to see all the content of the results field.

Use JOIN. It allows, among other things, the table to be linked to itself. This allows data from different lines of the table to be linked to one line of the request. It's only worth using pseudonyms to keep calm.SELECT n.*, j1.company, j1.tara, j2.date FROM nook n JOIN jurnal j1 ON n.parent = j1.id JOIN jurnal j2 ON j2.id = j1.parent WHERE n.spisan is NULL OR n.spisan = 0;

As far as I'm concerned, you need to get a list of flowers in the category of published goods to get a list of flowers in the filter. I think it's gonna take a lot of time, no matter what the indices.Try to break the request for a few:Receive id published goods in category (select id from item where ...)For id goods, get unique coloursselect distinct id_color from iitem_color where id_item in (...))Try to figure out the speed of these requests first. If it is faster, it is possible to think about optimizing the indices to join, or to leave the data to several requests.If it doesn't work faster, then you can put the results in the category, for example, for 24 hours. Then only once a day, the visitor will wait eight seconds (one category). Or you can update the cron wheat so no one can wait.Or, for the last case, just in the filter, you'll get a list of all the flowers available, whether there are any or not.

Use the method. attr(...)which, in the name of the attribute, returns its meaning:First:Document page = Jsoup.parse("<div class=\"story\" data-story-id=\"4591077\" data-visited=\"false\" data-story-long=\"false\">"); int dataStoryId = Integer.parseInt(page.select("div.story").attr("data-story-id")); Two:Document page = Jsoup.parse("<div class=\"story__date\" title=\"2 ноября 2016 в 23:58\">7 дней назад</div>"); String title = page.select("div.story__date").attr("title");

Quickly:switch to use innoDB instead myisam (inhales deeply) Like and add the pole index to♪If this is not enough:Take the contents of the pole to in a separate table so that one line is one e-mail♪ Organize one to many using https://dev.mysql.com/doc/refman/5.5/en/create-table-foreign-keys.html ♪Something like:CREATE TABLE IF NOT EXISTS `mails` ( `id` int(11) NOT NULL AUTO_INCREMENT, `to_id` varchar(255) NOT NULL, `object` varchar(30) NOT NULL, `data` text NOT NULL, `temp_id` int(11) NOT NULL, `mailing_id` int(11) NOT NULL, `priority` tinyint(1) NOT NULL, `send_time` datetime NOT NULL, `error` tinyint(1) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS recipients ( id int unsigned not null auto_increment, rec_id int unsigned not null, to varchar(255) NOT NULL not null, INDEX pn_rec_index(rec_id), FOREIGN KEY (rec_id) references mails(to_id) on delete cascade, PRIMARY KEY(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

select u.* , ifnull(c.cnt, 0) as entry_count from users u left join (select count(*) as cnt, o.user_id from objektos o GROUP BY user_id) as c ON u.id = c.user_id order by u.id desc

You can go another way. ng-model will keep the entire object chosen and don't need to be transferred to refreshing.Witch:<div ng-controller='myController'> <select name="region" id="region_of_birth" class="form-control" ng-options="item as item.Name for item in regionsList" ng-model="model.BornRegion" ng-change="refreshBornDistricts()"> <option></option> </select> {{ model.BornRegion }} </div> Controller:$scope.refreshBornDistricts = function () { console.log($scope.model.BornRegion); };

With help css a request can be made options (sighs) option[selected] but you can't turn up select♪They say there'll be a pseudal class soon. :has Which can be checked by itself. select to get the necessary sector. But even if he shows up tomorrow, in reality, not soon. That's why it's only now. javascript ♪ // получаем все option с selected let selected = document.body.querySelectorAll( 'option[selected]' ); // преобразуем в массив с select let selectAll = Array.from( selected ).map( item => item.closest( 'select' ) ); // добавляем какие-то классы selectAll.forEach( select => select.classList.add( 'some-class' ) );