New answers tagged select
0
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 ...
0
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 =...
1
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'...
0
I think I figured it out, I read about advisory locks and they seem to fit the bill perfectly.
Basically for each query one of the three queries I talked about in the question, I first all a query with select pg_advisory_xact_lock(id), where id is a unique identifier for this query.
This makes all three queries impossible to run at the same time, giving me ...
0
In modern versions of SQLite 3.25+, you can do this very efficiently with window functions. No self-joins needed.
SELECT
filename,
id,
name,
value
FROM (
SELECT *,
COUNT(CASE WHEN name LIKE '%USER%' THEN 1 END) OVER (PARTITION BY id) usr,
COUNT(CASE WHEN name LIKE '%PASS%' THEN 1 END) OVER (PARTITION BY id) pass
FROM files
) f
...
0
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 ...
0
It's not entirely true that a leading '%' in a LIKE condition prevents use of an index. If you have a NONCLUSTERED index with a key column on the column in question and few INCLUDES, a scan of a NONCLUSTERD index could still be faster than a SCAN on the CLUSTERED index. Picture a table with 30 columns, for example. The difference could be 1 million logical ...
0
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 ...
Top 50 recent answers are included