-2

I have a simple query that I just want to return the most recent date for.

I can't figure out why I can't do this:

enter image description here

  • 4
    Please don't use screenshots for code snippets: meta.stackoverflow.com/questions/285551/… – Kris Johnston Feb 17 '17 at 18:31
  • 2
    Your screenshot shows invalid standard SQL. Which DBMS are you using? – a_horse_with_no_name Feb 17 '17 at 18:36
  • 2
    Remove pc.recalc_date from the Group by since you want the Max of that column. – WEI_DBA Feb 17 '17 at 19:10
  • @a_horse_with_no_name with invalid you mean the single quotes aliases, I guess? – ypercubeᵀᴹ Feb 17 '17 at 20:30
3

It looks to me like 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.

1

You have added pc.cost as a group by column. This means your query will pull all (distinct) values of pc.cost and all records associated with them.

You either need to remove pc.cost from your SELECT and GROUP BY, or you need to put a function (MAX, MIN, etc) on pc.cost as well (and remove pc.cost from the GROUP BY anyway).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.