New answers tagged

0 votes

How do I partition my database by rowid and query the partitions properly?

first_value() and last_value() are window functions and not aggregate functions. Aggregate functions compute over all rows in the GROUP BY, but window functions compute over the results of the group ...
Andrea B.'s user avatar
  • 1,483
0 votes

Select random records until a cumulative total is exceeded

I thought White Owl's answer was good but after a few queries I noticed some issues: When I attempted to bring the running total over from below_threshold to include in one_more I noticed that the ...
Ashley's user avatar
  • 103
2 votes

Snowflake won't allow an analytic function to be renamed

lag(...) over(...) is an entire expression, you cannot split it at will. The correct syntax is select a1 , a2 , lag(a2) over (partition by a1 order by a2) new_col_name from a
mustaccio's user avatar
  • 25.7k
2 votes
Accepted

Snowflake won't allow an analytic function to be renamed

SQL server has another syntax for creating tables from another select. Also you need to do that all columns named --drop table a; create table a ( a1 varchar, a2 varchar ); insert into a ...
nbk's user avatar
  • 7,999
3 votes
Accepted

Query for single return meeting multiple criteria and values

Start by joining the tables together, with the right filters on vendors in place. Looks like you need to group by subGrp and cusID to check for those Y, F, J values, then group again by subGrp to get ...
Charlieface's user avatar
  • 12.5k
1 vote
Accepted

Select random records until a cumulative total is exceeded

In other words, the task is to get a list of records with a sum less then threshold, and one additional record to go over the threshold? This can be achieved by using with and union clauses: with ...
White Owl's user avatar
  • 772
0 votes

In MS Studio how to SELECT from an existing SQL file

I do that a lot. Yes I have my library of scripts, for instance, to check if the replication is healthy, but it could also be just a query. the way I do is - I use powershell. basically this shows me ...
Marcello Miorelli's user avatar
0 votes

Delete million of rows from parent and child tables in SQL Server

Are there more records to keep or to delete? If there are more records to delete than to keen, then it maybe more easy and faster to create new tables insert the records to keep from the old tables ...
Nikunj Bhatt's user avatar
2 votes
Accepted

Query to find when a specific current value changed

What you need to do is join table2 to it self and filter out the amount in when it is less meaning when it's changed to 0000 and then filter the date to leatest this one specialy for workID 251 cause ...
Ammar Talal's user avatar
1 vote
Accepted

How to update a Postgres row atomically, with a computed value that depends on the new values

The best practice is not to store redundant data in a database. My solution would be CREATE TABLE real_avg ( user_id bigint PRIMARY KEY, numer double precision, denom double precision ); ...
Laurenz Albe's user avatar
  • 50.3k
0 votes

How to "move" data from rows to columns with aggregate data?

There are two ways to move the rows to columns either use Pivot or the case, for your example to move it to column using case, select date(created), sum(case when event_type = 'a' then 1 else 0 end) a,...
Ammar Talal's user avatar

Top 50 recent answers are included