All Questions
16 questions
1
vote
1
answer
320
views
Query for repeating sequences in SQLite
I have an SQLite DB, let's assume the id can be considered as the order of rows, I want to find repeating sequences in the table according to certain criteria.
consider this example (http://sqlfiddle....
- 167
6
votes
1
answer
780
views
Unused columns in WHERE clause
I found this query in our codebase:
DELETE FROM "Foo"
WHERE ("Foo"."Id", "Foo"."CreatedAt")
IN (SELECT "f"."Id", "f".&...
- 205
0
votes
2
answers
73
views
Create a DELETE statement that deletes Models that haven’t had a purchase since December 31st 2014
We are given 5 tables.
Orders
Order number (Primary Key)
Date of order
Customer ID (Primary Key for Customers table)
Serial Number (Primary Key for Machines table)
Customers
ID ...
1
vote
1
answer
38
views
Create a DELETE statement that deletes Manufacturers with less than 10 sales
Orders:
Order number (Primary Key)
Date of order
Customer ID (Primary Key for Customers table)
Serial Number (Primary Key for Machines table)
Customers:
ID (Primary Key)
Name
Phone Number
Street (ie. ...
2
votes
1
answer
47
views
SQL daily value (today value - yesterday value)
I have a database where there are sales .
+----+-----------+------------+
| ID | Quantity | DATE |
+----+-----------+------------+
| 1 | 40 | 2020/10/10 |
| 2 | 0 | 2020/10/01 |...
- 23
2
votes
0
answers
628
views
Correlated subquery with common table expressions in SQLite
Yesterday I achieved a 500x performance boost after rewriting an EXISTS with IN. I did some investigation to find out why and finally came to the following example. Basically, we draw 5 lucky numbers ...
- 131
1
vote
2
answers
278
views
Getting a query to work on SQLite as well as on MySQL
I have a subquery with a condition in the main query where clause.
Here is the sample schema
CREATE TABLE `payments` (
`id` int(11) NOT NULL,
`discount_id` int(11),
`amount` int(11)
);
...
0
votes
1
answer
58
views
How to rank an aggregation in each newly formed group?
The problem below that I would like to solve sounds quite simple but it is quite difficult for me to arrive at the solution with SQL.
Let's say I have a simplified ERD that looks as follows:
This ...
- 101
6
votes
1
answer
433
views
Why an incorrect JOIN using correlated sub-query is so much slower
I'm doing some fairly lightweight data massaging/cleaning and ran into a problem where one version of JOIN using a correlated sub-query (probably an erroneous one) ran much much slower than what I ...
- 163
2
votes
3
answers
122
views
Get the latest change in value from a streak of values
I'm looking for a SQL query which returns the most recent change (date) in a time series of sensor data.
Basically I need to know when the sensor with id==i had a value change for the first time from ...
- 121
0
votes
1
answer
51
views
Sqlite - Query on table?
I have a lot of records like this (over 6000) in this table Lat and Lng is same and I should select base these fields (lat,lng) :
I need to computing on these records ( and )
I need to get first ...
- 103
5
votes
1
answer
556
views
Spatialite: use of virtual spatial index for distances
I'm currently learning to use spatial access methods for query optimization. I'm going through the examples in spatialite cookbook and got stuck in here
http://www.gaia-gis.it/gaia-sins/spatialite-...
- 51
0
votes
1
answer
155
views
SQLITE table joining with group
MESSAGE TABLE USERS
msg_id | msg_to | msg_from | msg_body | msg_date user_id | user_name
1 1 2 hi 22/1/14 ...
- 349
0
votes
2
answers
542
views
Problem in Nested Query [closed]
I have a query like this
SELECT m.title
FROM Movie AS M
WHERE
(SELECT MAX(r.stars)
FROM rating AS r
WHERE r.mID = M.mID);
I want to show the MAX(r.stars) in my result set, what am I doing wrong?
0
votes
1
answer
2k
views
Delete with subquery with no unique columns
I have a table LIKES (ID1, ID2). Both id1 and id2 are non unique columns.
Database is SQLite
delete from LIKES where ID1 = 10 /// Wrong there more then one record with ID1=10
delete from LIKES ...
- 203
11
votes
3
answers
4k
views
SQLITE: A problem of tags and products
I'm searching for a way to create a query to do the following:
Let's consider 3 tables:
products: List of products
tags: list of tags
tag_ties: table used to associate a tag to a product
Let's ...
- 213