Querying related to grouping

[paradis222]

I have a question related to grouping data
I have 2 fields, id and Serial No. id is unique and Serial No can be duplicate
the query like this:

<cfquery name=&quot;recordcheck&quot; datasource=&quot;support&quot;>
select Max(id) as tempid, Serialno from loan where Serialno IN (#QuotedValueList(tracking.Serialno)#) group by Serialno
</cfquery>

I would like to select all record in table &quot;loan&quot;, if there is duplicate serial no in the table, then select serial no with a large value of &quot;id&quot; , how can i do that? thanks!

 

[r937]

use a correlated subquery to get the largest id for each serialno group --

[tt]select Serialno, id
from loan X
where id =
( select max(id)
from loan
where Serialno = X.Serialno )
and Serialno in
(#QuotedValueList(tracking.Serialno)#)[/tt]

rudy

http://r937.com/

 

[paradis222]

I think I should amend this query... but how can i amend for the same result and function?

I would like to select all record in table &quot;serialrecord&quot;, if there is duplicate serial no in the &quot;loan&quot; table, then select serial no with a large value of &quot;id&quot;

<cfquery name=&quot;serial&quot; datasource=&quot;support&quot;>
SELECT DISTINCT serialrecord.Serialno AS SN, serialrecord.Partno as PN, serialrecord.Description as Des, serialrecord.Vendor AS Ven, serialrecord.SystemID as Sys, serialrecord.Po as Porder, serialrecord.Vendorwarranty as Venwar, claim.Serialno, claim.replacedsn as repsn, claim.rma as rmano, claim.replacedpartno as repart,loan.Serialno, loan.id, loan.status as newstatus from ((serialrecord left outer join claim on serialrecord.Serialno = claim.Serialno)left outer join loan on serialrecord.Serialno = loan.Serialno )where serialrecord.Serialno IN (#QuotedValueList(tracking.Serialno)#)
</cfquery>

Thankss!!!