SQL - Select the results for MAX(date) for multiple columns in single line

sql select max value from multiple rows
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