New answers tagged query
0
As mustaccio pointed out you must set a partition in the ROW_NUMBER() function, just to start the counter again for each LocationId.
I've set up the next example:
create table locations (id int identity primary key, location_id varchar(10));
insert into locations values
('001'),('001'),('001'),('001'),('001'),('001'),('001'),('001'),('001'),('001'),
('001'...
0
On MySQL 5.6 you can use a subquery to get the date:
select
t2.map_id,
max(t2.score),
(select t1.date
from test t1
where t1.map_id = t2.map_id
and t1.score = max(t2.score)) as date
from
test t2
group by
t2.map_id
SQL Fiddle
0
Check this solution:
WITH
cte1 AS ( SELECT *,
SUM(CASE WHEN Quantity > 0
THEN Quantity END) OVER (PARTITION BY ItemId
ORDER BY InventoryDate) incomeQuantity,
SUM(Quantity) OVER (PARTITION BY ItemId
ORDER BY InventoryDate) balance
...
1
This is what I ended up going with.
--====================================================================================================================================================
-- Get tables in Snapshot replication for the selected DB.
--===============================================================================================================...
1
Think of it as different queries. How much it cost to navigate a B-Tree to find zero rows? That is what happens for those "extra partitions". Those that don't contain any data for your range and which are skipped with your second query alternative. If you don't have too many partitions, then this extra cost will be very low. But as number of partitions ...
0
There is no way around unnesting the array for every row (the drawbacks of de-normalizing data). But you don't need a cross join, an EXISTS condition should work just fine.
select *
from demo d
where exists (select *
jsonb_each(d.rawjson) as r(jdoc)
where (jdoc ->> 'a')::int < 2
and (jdoc ->> 'b')::...
0
I think the best you can get with SQL is Order(N^2) or maybe Order(N^3).
I suggest you could do much better with application code.
Have a bunch of buckets (initialized to 0); one per minute.
SELECT the time ranges
For each minute in each range increment the appropriate bucket.
1
I'd like a query that could produce this sort of result
Look at
WITH RECURSIVE
cte1 AS ( SELECT CAST(DATE_FORMAT(MIN(StartTime), '%Y-%m-%d %H:%i:00') AS DATETIME) mintime,
CAST(DATE_FORMAT(MAX(EndTime), '%Y-%m-%d %H:%i:00') AS DATETIME) maxtime
FROM srctable ),
cte2 AS ( SELECT mintime timechunk,
mintime ...
0
Another case for EXISTS:
SELECT *
FROM schema.table t
WHERE EXISTS (
SELECT FROM schema.table t1
WHERE lower(t.email) = lower(t1.email)
AND t.ctid <> t1.ctid
)
ORDER BY lower(email), email;
If you have a PK, use it instead of ctid. Related:
How do I remove duplicate records in a join table in PostgreSQL?
This returns every ...
0
You need to exclude the rows from each other:
SELECT c.email
FROM schema.table c,
schema.table d
WHERE lower(c.email) = lower(d.email)
AND c.email <> d.email;
One question you will need to answer (and I will update my answer if relevant) -- are you using ILIKE because you want partial matches too? Like, are bert@foo.com and RObert@foo....
1
I think you are looking for a FULL OUTER JOIN:
SELECT COALESCE(a.id,b.id,c.id), COALESCE(a.col1,'NULL'), COALESCE(b.col2,'NULL'), COALESCE(c.col3,'NULL')
FROM table1 a
FULL OUTER JOIN table2 b ON a.id=b.id
FULL OUTER JOIN table3 c ON b.id=c.id;
or, if you want blanks instead of strings:
SELECT COALESCE(a.id,b.id,c.id), a.col1, b.col2, c.col3
FROM ...
1
SELECT d.id deviceid, u.id userid, dp.preferenceid
FROM Users u, Devices d, Device_Preference dp
WHERE u.id = dp.userid
AND u.id = d.userid
ORDER BY 1,3
fiddle
1
You are not seeing anything in the command window because you are telling SQLCMD.EXE to direct all output to a file due to using the -o switch. If you want results to go to a file and messages to go to the screen (and not into the file), then you cannot use the -o switch.
Instead, you will need to manually redirect output (i.e. "stdout") to the file using &...
1
If you're open to using PowerShell, you can do something like this.
sqlcmd -i inputpath -S myServer -E -d MYDB | Tee-Object -file "C:\log.xls"
1
While your solution should basically do the job:
SELECT type FROM all_types WHERE type NOT LIKE ALL(SELECT type||'%' FROM completed_types)
Note some corner-case problems:
If completed_types.type isn't defined NOT NULL and you introduce even a single NULL, your query will not return any rows. So make sure it's NOT NULL.
If all_types.type allows NULL, those ...
0
I managed to solve this on my own, with a very simple solution. I needed to do:
SELECT type FROM all_types WHERE type NOT LIKE ALL(SELECT type||'%' FROM completed_types)
This should not have taken me a handful of hours.
1
What I would do is something like the process outlined below. I've left in links to pages which I found helpful and I've also left in fields which aren't strictly necessary. I'll outline my thought process as I go.
CREATE and populate doctor and patient tables:
CREATE TABLE doctor
(
doctor_id INTEGER NOT NULL AUTO_INCREMENT,
doctor_name VARCHAR (50) ...
2
You could try along
SELECT
id
, createdAt
FROM (
SELECT
id
, createdAt
, RANK() OVER (PARTITION BY OtherId ORDER BY createdAt) rnk
FROM Table1
) AS S
WHERE rnk = 8
;
The inner SELECT groups the records by OtherId, orders and ranks them by createdAt. The outer SELECT picks the record by some number.
The CTE (common table expression) @...
0
That sounds like it could be done using a NOT EXISTS expression
select inv.*
from invoice inv
where not exists (select *
from allocation alo
where inv.invoice_rk = alo.invoice_id_pk)
and inv.allocated_amount is not null;
1
In MongoDB 3.6+ you can add a pipeline to your $lookup stage to include additional join conditions.
A solution was less obvious than I expected since your source lookup field is an array, but the following should return the expected outcome:
db.collection1.aggregate([
{ $match: {
_id: "col1id1"
}},
// Filtered lookup based on agg ...
1
The query returns the same record multiple times.
Variants:
one of source tables (or even both) contains duplicates;
the records are NOT duplicates, there exists at least one field (in a record pair which seems to be duplicates for you) where values differs.
the table Keys contains multiple equal values of Bnumber, but that is required because 1 Book ...
0
If you wish to literally work with syllables, you should find a natural language processing tool which does such things. If it is in Python or Perl or Tcl, you should be able to import the functionality into PostgreSQL using the respective PL language.
If you are happy to work with reduplicated sequences of letters without regard to their phonology, you can ...
1
Ok, one possible way to resolve this is through the use of GROUP_CONCAT to implode the multiple values returned by the subquery into a single string:
Here's how my subquery has changed within the top-level SELECT:
SELECT
...
...
(SELECT
GROUP_CONCAT(kits.uid ORDER BY kits.updated_at ASC SEPARATOR ', ')
FROM
kits
WHERE
kits....
0
The syntax for SQL commands is not any different in large environments. For business applications, the user will typically interact with a client - often a desktop or web application. Depending on what language the client is written in, the code will use different types of function calls to connect to the SQL server, run queries, and retrieve data. For ...
1
Your problem is that you are inner joining both subqueries together, meaning that if there are no rows for the join condition (in your case, p.sender_mark = b.sender_mark) in one of the subqueries, you won't get a row in the results for the row in the other subquery that does exist.
You could get around this by turning the join into an outer join, but (IMO) ...
0
What version of Postgres are you using?
If I understand correctly, you would like to match records with similar words. There are several ways to approach this. I'd suggest taking a look at a few features and seeing if one sounds like a good match for you.
The pg_trgrm extension included with standard distributions is worth considering:
https://www....
0
In my WHERE clause for the STUFF, I needed to add a check for the ItemId instead of the TableId. I also had to add additional joins to the Items, dmItems, and dmStatuses tables inside of the STUFF and use those tables when concatenating the STUFF instead of outer tables that I was using previously.
Top 50 recent answers are included
