Custom SQL GROUP BY Clause

Question

I have a very customized SQL query that I am having problems implementing. I am using SQL-Server-2008.

I have only one table in this query, but I am looking for very specific data. The requirements for this query are:

For each DISTINCT PartNumber (column), I need to select the NEWEST (max) PO (column) to be selected. However, there is another column named "Receipt" where if it contains a value at all, then the PartNumber should be excluded all together.

I am somewhat familiar with GROUP BY clauses and CASES for selections, but I'm not sure how to tie all I know together into one working query...

Any help is greatly appreciated! Thanks in advance =).

Answer 1

SELECT Partnumber, MAX(PO)
FROM MyTable t1
WHERE NOT EXISTS (SELECT 1
                  FROM MyTable
                  WHERE (Receipt <> '0'
                         OR Receipt <> '')
                  AND Partnumber = t1.partnumber)
GROUP BY PartNumber

The NOT EXISTS here will exclude any row that has a partnumber for which a receipt is populated anywhere in the table.