products table
Get products where amount=0 and versionno=highest for a particular productno:
SELECT ProductNo, MAX(VersionNo) AS VersionNo
FROM Products
WHERE Amount = 0
GROUP BY ProductNo
HAVING MAX(VersionNo) = (SELECT MAX(VersionNo)
FROM Products
WHERE ProductNo = Products.ProductNo)
I am expecting
| productno | versionno | amount |
|---|---|---|
| 10 | 3 | 0 |
| 30 | 2 | 0 |
New contributor
Reddy Eshwar is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
2
Proper use of aliases is solving this problem:
SELECT p1.ProductNo, MAX(p1.VersionNo) as VersionNo
FROM Products p1
WHERE p1.Amount = 0
GROUP BY ProductNo
HAVING MAX(p1.VersionNo) = (SELECT MAX(p2.VersionNo)
FROM Products p2
WHERE p2.ProductNo = p1.ProductNo)
see: DBFIDDLE
You can make use of Analaytical query i.e. over clause with max to get the required result.
SQL fiddle
-- Create the test table
CREATE TABLE test (
ProductNo INTEGER NOT NULL,
VersionNo INTEGER NOT NULL,
Amount NUMERIC
);
-- Insert data into the test table
INSERT INTO test (ProductNo, VersionNo, Amount) VALUES
(10, 1, 100),
(10, 2, 120),
(10, 3, 0),
(20, 1, 45),
(20, 2, 0),
(20, 3, 55),
(20, 4, 78),
(30, 1, 120),
(30, 2, 0);
with CTE1 AS
(
select productno, versionno, max(versionno) over(partition by productno) max_versionno, amount
from test)
select productno, versionno, amount from cte1 where amount = 0 and versionno = max_versionno;