SQL - Select the results for MAX(date) for multiple columns in single line
sql select min value from multiple columns
sql max group by multiple columns
mysql select max value from multiple columns
sql select max value based on another column
sql server max of two values
sql select min date from multiple columns
sql select max value from multiple tables
I would like to get the last order date from a table split into sales as purchase order
Please see below example
EXAMPLE
PRODUCT ORDER_DATE ORDER_PRICE ORDER_TYPE 0000016920 2018-05-11 135.440 Purchase Order 0000016920 2018-05-11 135.440 Sales Order 0000022670 2018-08-01 0.010 Sales Order 0000024280 2018-09-25 661.9757 Purchase Order 0000024280 2018-09-25 661.9757 Sales Order 0000025560 2018-03-14 265.5953 Sales Order 0000025560 2018-01-16 224.6733 Sales Order 0000025560 2018-03-14 265.5953 Purchase Order 0000025560 2018-03-01 224.6733 Sales Order 0000025560 2018-01-16 224.6733 Purchase Order 0000025590 2018-10-02 841.3115 Sales Order 0000025590 2018-10-02 841.3115 Purchase Order 0000025960 2018-10-02 1070.6024 Purchase Order 0000025960 2018-10-02 1070.6024 Purchase Order 0000025960 2018-10-02 1013.0212 Purchase Order 0000025960 2018-01-11 747.2314 Purchase Order
DESIRED OUTPUT
PRODUCT SALESDATE SALES_PRICE PURCHASE_DATE PPRICE 0000016920 2018-05-11 135.4400 2018-05-11 135.4400 0000022670 2018-08-01 0.0100 2018-08-01 0.0100 0000024280 2018-09-25 661.9757 2018-09-25 661.9757 0000025560 2018-03-14 265.5953 2018-03-14 265.5953 0000025560 2018-01-16 224.6733 2018-01-16 224.6733 0000025560 2018-03-01 224.6733 2018-03-01 224.6733 0000025590 2018-10-02 841.3115 2018-10-02 841.3115 0000025960 2018-03-01 747.2314 2018-03-01 747.2314 0000025960 2018-10-02 1056.2071 2018-10-02 1056.2071 0000025960 2018-10-02 1070.6024 2018-10-02 1070.6024 0000025960 2018-01-12 747.2314 2018-01-12 747.2314
I would just use conditional aggregation:
select product, max(case when order_type = 'Sales Order' then order_date end) as sales_date, max(case when order_type = 'Sales Order' then order_price end) as sales_price, max(case when order_type = 'Purchase Order' then order_date end) as purchase_date, max(case when order_type = 'Purchase Order' then order_price end) as purchase_price from example group by product;
Find MAX value from multiple columns in a SQL Server table, Find MAX value from multiple columns in a SQL Server table find the maximum or minimum value from different columns in a table of the same data type. table, but we want to choose the maximum date from UpdateByApp1Date, Our task is to find the last update date for each row using the three dates I would like to get the last order date from a table split into sales as purchase order. Please see below example . EXAMPLE. PRODUCT ORDER_DATE ORDER_PRICE ORDER_TYPE 0000016920 2018-05-11 135.440 Purchase Order 0000016920 2018-05-11 135.440 Sales Order 0000022670 2018-08-01 0.010 Sales Order 0000024280 2018-09-25 661.9757 Purchase Order 0000024280 2018-09-25 661.9757 Sales Order 0000025560
I believe you need something like that:
SELECT ISNULL(Sales.PRODUCT,Purchase.PRODUCT) AS Product ,ISNULL(Sales.ORDER_DATE ,Purchase.ORDER_DATE ) AS OrderDate ,Sales.ORDER_PRICE AS SalesPrice ,Purchase.ORDER_PRICE AS PurchasePrice FROM (SELECT * FROM Tbl AS T WHERE ORDER_TYPE='Sales Order') AS Sales FULL OUTER JOIN (SELECT * FROM Tbl AS T WHERE ORDER_TYPE='Purchase Order') AS Purchase ON Sales.PRODUCT=Purchase.PRODUCT AND Sales.ORDER_DATE=Purcahse.ORDER_DATE
[Solved] How to get max date of a column along with its , I want only one row Have a look here: SQL GROUP By and the "Column 'name' is invalid in the What you need to do is two queries: One to return the max Date for SELECT batch_no, MAX(bill_Date) FROM MyTable GROUP BY with the whole result being based on the first difference encountered I find your article very helpful. I tried the UNPIVOT function to find the MAX value of multiple columns in my tables. However, I am having a problem filtering the results. It is getting the maximum value, the problem is, that value shows in every row (every record, regardless of record ID).
You can use something like this
SELECT (SELECT MAX(DATE1) from tbl) as date1, (SELECT MAX(DATE2) from tbl) as date2
Returning a result set with multiple rows based on max date , You simply want: SELECT [Customer ID], MAX([Some Date]) AS[Latest Date] FROM[Cust Date TABLE] GROUP BY [Customer ID];. Ok - you've revised it. Re: select max date from a group of columns of type date Sentinel Oct 8, 2007 7:24 PM ( in response to 519688 ) so use the NVL on each column. and set it to something ridiculous, so it will always be less than the other values. and then, if the ridiculous value shows up, it means all the columns were null for that row.
Find max and min date over multiple fields of the same record , SELECT ID, MinF, MaxF FROM @T CROSS APPLY ( SELECT MIN(f) AS MinF, MAX(f) AS SQL Fiddle. All variants return the same result SELECT with DISTINCT on multiple columns and ORDER BY clause. You can use an order by clause in the select statement with distinct on multiple columns. Here is an example: SQL Code: SELECT DISTINCT agent_code,ord_amount FROM orders WHERE agent_code='A002' ORDER BY ord_amount; Output:
Find Latest Date from Multiple Columns – SQLServerCentral, Find Latest Date from Multiple Columns – Learn more on the It'll be messy, but SQL doesn't have a 'max across columns' function. SELECT MA.MaxDate. FROM <mytable> AS MT. CROSS APPLY ( This doesn't work as the top 1 limits the output to a single row The CROSS APPLY results again:. How to Calculate Multiple Aggregate Functions in a Single Query Posted on April 20, 2017 April 23, 2017 by lukaseder At a customer site, I’ve recently encountered a report where a programmer needed to count quite a bit of stuff from a single table.
Max Date from multiple columns – SQLServerCentral, Max Date from multiple columns – Learn more on the That gives me all the payments instead of just the one line I want. CREATE SELECT id On SQL Server 2014 I would think this is a great use case for LAST_VALUE(). When I query a database that includes a particular field (Condition), it returns multiple rows of Conditions associated with the same result from another column (NCT_ID). See sample output below. NCT_ID CONDITION
Comments
- I would like to help you but can you include your query? And what rdbms you are using? Sql are just language