Solved

MAX Number

Posted on 2007-11-26
4
295 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:ziwez0
4 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20349347

SELECT t.serialno, t.partno, t.shippeddate, t.fdescript, f.fcusrchr1
FROM         MACHINESEARCH t
 WHERE (t.PartNo LIKE @PartNo%) 
  AND (t.SerialNo = @SerialNo
  and t.shippeddate = ( Select max(i.shippeddate)
            from MACHINESEARCH i
           WHERE (i.PartNo LIKE @PartNo%) 
             AND (i.SerialNo = @SerialNo
            )

Open in new window

0
 
LVL 6

Expert Comment

by:Rajesh_mj
ID: 20349433

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 

Open in new window

0
 
LVL 11

Accepted Solution

by:
yuching earned 500 total points
ID: 20355884
SELECT SerialNo, PartNo, ShippedDate, fdescript, fcusrchr1
FROM         MACHINESEARCH a
INNER JOIN (
           SELECT SerialNo, Max(PartNo)  As MaxPartNo
           FROM    MACHINESEARCH
           WHERE PartNo LIKE @PartNo% AND SerialNo = @SerialNo
           GROUP BY SerialNo
) b ON a.SerialNo = b.SerialNo and a.PartNo = b.MaxPartNo

GROUP BY SerialNo, PartNo, ShippedDate, fdescript, fcusrchr1
0
 
LVL 2

Author Closing Comment

by:ziwez0
ID: 31410958
Thank you
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question