Why does SQL server max date not working?

I guess you don't quite understand how to use GROUP BY. A query with GROUP BY will identify the unique sets of values for the grouped-by columns, and then will allow you to use aggregate functions on any other rows in the data set. So, first you've said, give me the unique values for the four columns in your query.

• Then, you say, give me the maximum pc.recalc_date for all the rows identified by each of the unique value sets.
  
• Since pc.recalc_date is one of the values you're grouping by, all the records in each group will have the same pc.recalc_date.
  

I'll assume that, for each part # and description, you want the date of the most recent change in cost, and what that cost was.Without more information, it's difficult to give you a specific solution and guarantee it will work. We don't know what database you're using, not to mention what version of that database. Similarly, we don't know the data types and lengths of your columns. I believe the most generic answer would be a two-step process. First, identify the date of the most recent price change for each part; then, get the corresponding price. I'll be assuming your part numbers are up to 20 characters long, your description up to 250 characters long, and that the type of your pc.recalc_date column is datetime; replace with valid values for your system. That would look something like this (assuming you have access to temporary tables in

your language): CREATE TABLE #latest_price_change (part_no varchar(20), part_desc varchar(250), recalc_date datetime)

INSERT INTO #latest_price_change (part_no, part_desc, recalc_date) SELECT pvp.part_no, pvp.name, MAX(pc.recalc_date) FROM part_v_part as pvp JOIN part_v_part_cost as pc ON pvp.part_key = pc.part_key WHERE pvp.part_status <> 'inactive' AND pvp.part_status <> 'obsolete' AND pvp.part_source_key = '373' AND pvp.part_no = '90-0018' GROUP BY pvp.part_no, pvp.name SELECT lpc.part_no as 'Part No.', lpc.part_desc as 'Description', lpc.recalc_date as 'Previous Update', pc.cost as 'Previous Std. Cost' FROM #latest_price_change as lpc JOIN part_v_part as pvp ON lpc.part_no = pvp.part_no AND lpc.part_desc = pvp.name JOIN part_v_part_cost as pc ON pvp.part_key = pc.part_key AND lpc.recalc_date = pc.recalc_date

After setting up a part_v_part and a part_v_part_cost table that returned the same three lines as yours did when using your query, I just got back the last line with my query.

FYI - I tested this with MS SQL Server 2012.

Hope this helps.

Here is the syntax to find max and min date in SQL

1. SELECT MIN(column_name) FROM table_name. WHERE condition;
   
2. SELECT MAX(column_name) FROM table_name. WHERE condition;
   
3. Example. SELECT MIN(Price) AS SmallestPrice. FROM Products;
   
4. Example. SELECT MAX(Price) AS LargestPrice. FROM Products;