I have a table with ID, Date, Value. For each row, I want to return the most recent non-null value where Date <= Date minus 1 year. I’m trying variations of window functions with last_value or lag, and partitioning on ID, order by Date, with the IGNORE NULLS clause. However, I’m not sure where …
Tag: sql
How can I concatenate field values together from other records in same table?
I have a simple table as below in MySQL. All I want to do is group records by PARENT ID and concatenate ATTRIBUTE records for that PARENT ID into the TYPE=’SIMPLE’ record. UNIQUE ID TYPE Parent ID Attribute MYID1 SIMPLE MYID1 MYID2 VARIANT MYID1 WHITE MYID3 VARIANT MYID1 BLUE So the result would b…
Transfer data from one table to another with both table having same id, in Postgres
I have 2 tables. TBL_A and TBL_B. I want to update TBL_A so that A_NAME = B_NAME, where A_LA = B_LA. How do I do this? This is just s sample data. In the real table, I have thousands of records on each table. I tried connecting both tables using View, but it seems that the view can’t be updated.
UPDATE RECORD 2 FIRST LATTER IN MYSQL [closed]
Closed. This question needs debugging details. It is not currently accepting answers. Edit the question to include desired behavior, a specific problem or error, and the shortest code necessary to reproduce the problem. This will help others answer the question. Closed 11 months ago. Improve this question How…
SQL – get rid of the nested aggregate select
There is a table Payment, which for example tracks the amount of money user puts into account, simplified as What is need is to receive a table with balance before payment moment, eg: Currently the select statement looks something like How can I rewrite this select to get rid of the nested aggregate selection…
How to LEFT JOIN with multiple cases
Assuming I have these two tables like: Table: tab1 valA valB valC valD 1 11 111 A 2 11 333 A 3 44 444 B 3 66 666 D Table: tab2 val1 val2 val3 val4 1 11 111 A 1 22 222 A 2 44 333 B 3 55 555 A 3 66 666 D I have 3 cases that
SQL Finding Dupe Names with Distinct ID
I have a list of customers, and a bunch of them are duplicates (‘Acme Inc’, ‘Acme, Inc’, ‘Acme Inc.’, ‘Acme, Inc.’) They all have different IDs. BUT, each ID also has multiple addresses. Something like… I have a function that normalizes the name so the fir…
SQL ROLLUP with COALESCE
The data: The query: The output I get is like this: It gives null every individual brand item not grouped, and I want the last null to be ‘Grand Total’ not ‘Monthly Total’. I tried putting PurchaseDate or Brand column in and out the ROLLUP but it doesn’t seem to work for the Bran…
Mysql where condition for single SELECT [not all]
I want to calculate the total gross amount of a CLIENT in all stores and in a specific store both in one query. Not repeating the same query twice as I did below and also not using group_by. My tables are: Clients and Orders Expected result: My query is Answer SUM with CASE WHEN for given store to get gross
How to use Left Join in subquery SQL Server?
I am trying to use left join in subquery in SQL Server. My query looks fine to me but it gives syntax error. This is my query: This is the error I’m getting: This is my table structure: Answer As @Chris mentioned, the query is bit incomplete. I guess you are trying to do something like this: This query …