finding nth record in sql server 7.0 on group

[paragsomani]

I want nth record from sql server 7.0 group wise(per account no). But the recordset has more than 1 million records.
I have tried this query:

select top 1 FAH_AMT from ( select top 2 tmp1=h1.FAH_TDATE ,FAH_AMT,FAH_ACC_NO,FAH_TRCDE from HPLFAHISTORY h1 where FAH_ACC_NO = hfd.fadl_acc_no order by h1.FAH_TDATE desc,FAH_TRCDE desc ) tmp2 order by tmp1 ASC ,FAH_TRCDE asc

But this is taking too much time more than 4 hrs (still executing)

Can you suggest me optimised solution.

Thanks in advance...

-Parag

 

[vodafrog]

You should ideally run this as a stored procedure on your SQL server, you can then call the procedure and get the result. See example code below, I assume you have an ODBC datasource, this also assumes you are not using trusted connections to your SQL server: -

You will need a userID, password Database name, IP address of the SQL server from the SQL Server Admin.

using the connection obj

con.open ("PROVIDER=SQLOLEDB;" _
&"network=dbmsscon;" _
&"PASSWORD=xxxxxxxxxxx;" _
&"PERSIST SECURITY INFO=TRUE;" _
&"USER ID=xxxxxxxxxx ;" _
&"INITIAL CATALOG=xxxxxxxxxxxxxxxxxxxx;" _
&"DATA SOURCE=IP")

then if you want a readonly result

use con.execute ("SPname")

if you want a writeable recordset then use the normal RS.open format.

you also have to have the SQL drivers installed on the machine using the
mdb.

Hope this helps.

 

[WayneRyan]

Parag,

I had a really hard time figuring out what your query should do.

I thing you wanted the "latest" amount for each account number. (I don't know what a TRCDE is).

I think your performance issues stem from a Cartesian join, you don't LEGALLY reference the main
table. I really can't believe that SQL accepted the --> hfd.fadl_acc_no reference.

The hfd part is not declared.

I think basically you're just joining the History table to itself. (No hfd.fadl_acc_no reference)

[U]select top 1 FAH_AMT[/U]                      <-- Fom HPLFAHISTORY  (But you're only getting a string of amounts,
                                          <--                    It doesn't have their account numbers.
                                          <--                    Nothing relates to account numbers.  That's not good.
                                          <--                    We REALLY need a PARENT account number here.
                                          <--
[U]from  (select top 2 tmp1 = h1.FAH_TDATE,[/U]  <-- Fom HPLFAHISTORY  (tmp1 - Invalid assignment, not like T-SQL)
[U]              FAH_AMT,[/U]                    <-- Fom HPLFAHISTORY 
[U]              FAH_ACC_NO, [/U]                <-- Fom HPLFAHISTORY 
[U]              FAH_TRCDE [/U]                  <-- Fom HPLFAHISTORY 
[U]       from HPLFAHISTORY h1[/U]  <-- You are not joining any tables
                             <-- Your data is all coming from the
                             <-- history table (This will "orphan" the fadl_acc_no number (see Cartesian below).
[U]       where FAH_ACC_NO = hfd.fadl_acc_no[/U]  <-- Not a valid join,
                                           <-- FAH_ACC_NO is in the history table
                                           <-- BUT, what is hfd?
                                           <-- Is it the main table?
                                           <-- The "main" table is HFD <-- we really need a parent (see above)

[U]       order by h1.FAH_TDATE desc,FAH_TRCDE desc) tmp2[/U] <-- tmp2 is the alias of the
                                                       <-- highest TDATEs and the
                                                       <-- highest TRCDEs
                                                       <-- BUT, they aren't related to
                                                       <-- the parent (No join).
                   
[U]order by tmp1 ASC[/U],  <-- I really don't think that tmp1 has a value here
                    <-- If it was meant for T-SQL, it'd be --> @tmp1
                    <-- Even at best, you're ordering by "the TOP 1 of the Top 2 of the TDATE, TRCDEs pairs.
                    <-- It's a cartesian product, it's gonna take a long time
[U]         FAH_TRCDE asc[/U]

Centering the query around your parent table, just look for the History Table entry with the
latest date.

I know the PK for your main table is "fadl_acc_no", so using "MainTable" ... <-- You used "hfd."

select MainTable.fadl_acc_no,      <-- That is the main table PK
       HPLFAHISTORY.FAH_AMT
From   MainTable Left Join HPLFAHISTORY On
         MainTable.fadl_acc_no = HPLFAHISTORY.FAH_ACC_NO
Where  HPLFAHISTORY.FAH_TDATE In (Select Max(HPLFAHISTORY.FAH_TDATE) <-- P.S. DO NOT TRY THIS IN ACCESS
                                                                     <-- Use a join instead ... JET will get slow!
                                  From   HPLFAHISTORY
                                  Where  FAH_ACC_NO = MainTable.fadl_acc_no)
Order By MainTable.fadl_acc_no

hth,
Wayne