ziwez0
asked on
MAX Number
Morning,
Im having a bad morning, im trying to return 1 record..
SELECT DISTINCT SerialNo, MAX(DISTINCT PartNo) AS PartNo, MAX(DISTINCT ShippedDate) AS ShippedDate, fdescript, fcusrchr1
FROM MACHINESEARCH
GROUP BY SerialNo, PartNo, ShippedDate, fdescript, fcusrchr1
HAVING (PartNo LIKE @PartNo%) AND (SerialNo = @SerialNo
the issue i have it returns 2+ records,
SerialNo | PartNo | ShippedDate | fdescript | fcusrch1
-------------------------- ---------- ---------- ---------- ---------
00010 12435-01 25/10/2007 abc 12
00010 12435-02 27/10/2007 addwww 12 <----------- i want this row only
I only want 1 row with the highest(max) PartNo
can someone show me how to do this please
Im having a bad morning, im trying to return 1 record..
SELECT DISTINCT SerialNo, MAX(DISTINCT PartNo) AS PartNo, MAX(DISTINCT ShippedDate) AS ShippedDate, fdescript, fcusrchr1
FROM MACHINESEARCH
GROUP BY SerialNo, PartNo, ShippedDate, fdescript, fcusrchr1
HAVING (PartNo LIKE @PartNo%) AND (SerialNo = @SerialNo
the issue i have it returns 2+ records,
SerialNo | PartNo | ShippedDate | fdescript | fcusrch1
--------------------------
00010 12435-01 25/10/2007 abc 12
00010 12435-02 27/10/2007 addwww 12 <----------- i want this row only
I only want 1 row with the highest(max) PartNo
can someone show me how to do this please
SELECT TOP 1 SerialNo, PartNo AS PartNo, ShippedDate AS ShippedDate, fdescript, fcusrchr1
FROM MACHINESEARCH
WHERE (PartNo LIKE @PartNo%) AND (SerialNo = @SerialNo)
ORDER BY ShippedDate DESC
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you
Open in new window