subquery pulling data based of another column 1

[mary1994]

The query below works correctly on a sample database with fewer records but on the production database it is skipping some of the records.
Here is my query:


SELECT T1.* INTO jump
FROM Table1 AS T1 INNER JOIN table2 ON T1.ID=table2.ID
WHERE (((T1.amt)<=(SELECT Max(Amt) FROM Table1 As T2 WHERE T2.ID = T1.ID AND (SELECT COUNT(*) FROM Table1 AS T3 WHERE T3.ID = T2.ID AND T3.Amt <= T2.Amt) <= table2.Audit)));

Someone from Microsoft Knowledge base helped me with this query. Here is the original link.

http://www.microsoft.com/office/com...ffice-access&lang=en&cr=US&sloc=en-us&m=1&p=1

Any help will be greatly appreciated as this part the assignment is causing me not to finish this project.

Thanks a million

 

[bborissov]

In case you use SQL Server 2005 or later:

[COLOR=blue]DECLARE[/color] @Table1 [COLOR=blue]TABLE[/color] (Id [COLOR=blue]int[/color], Amt [COLOR=blue]Numeric[/color](10,2), amt_type [COLOR=blue]varchar[/color](200), Trans_Type [COLOR=blue]varchar[/color](200))
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Table1 [COLOR=blue]VALUES[/color] (223,1.50,[COLOR=red]'cash'[/color],[COLOR=red]'credit'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Table1 [COLOR=blue]VALUES[/color] (223,2.03,[COLOR=red]'visa'[/color],[COLOR=red]'credit'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Table1 [COLOR=blue]VALUES[/color] (223,2.30,[COLOR=red]'MC'[/color],[COLOR=red]'paytogo'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Table1 [COLOR=blue]VALUES[/color] (234,0.50,[COLOR=red]'TC'[/color],[COLOR=red]'paytogo'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Table1 [COLOR=blue]VALUES[/color] (234,1.75,[COLOR=red]'cash'[/color],[COLOR=red]'credit'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Table1 [COLOR=blue]VALUES[/color] (234,0.90,[COLOR=red]'visa'[/color],[COLOR=red]'credit'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Table1 [COLOR=blue]VALUES[/color] (234,5.30,[COLOR=red]'visa'[/color],[COLOR=red]'credit'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Table1 [COLOR=blue]VALUES[/color] (234,1.25,[COLOR=red]'TC'[/color],[COLOR=red]'paytogo'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Table1 [COLOR=blue]VALUES[/color] (225,1.35,[COLOR=red]'MC'[/color],[COLOR=red]'paytogo'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Table1 [COLOR=blue]VALUES[/color] (225,2.24,[COLOR=red]'visa'[/color],[COLOR=red]'credit'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Table1 [COLOR=blue]VALUES[/color] (225,3.35,[COLOR=red]'MC'[/color],[COLOR=red]'paytogo'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Table1 [COLOR=blue]VALUES[/color] (226,2.17,[COLOR=red]'visa'[/color],[COLOR=red]'credit'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Table1 [COLOR=blue]VALUES[/color] (226,1.15,[COLOR=red]'TC'[/color],[COLOR=red]'paytogo'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Table1 [COLOR=blue]VALUES[/color] (226,1.50,[COLOR=red]'TC'[/color],[COLOR=red]'paytogo'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Table1 [COLOR=blue]VALUES[/color] (226,1.60,[COLOR=red]'visa'[/color],[COLOR=red]'credit'[/color])

[COLOR=blue]DECLARE[/color] @Query1 [COLOR=blue]TABLE[/color] (Id [COLOR=blue]int[/color], [COLOR=#FF00FF]Audit[/color] [COLOR=blue]int[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Query1 [COLOR=blue]VALUES[/color] (223, 2)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Query1 [COLOR=blue]VALUES[/color] (234, 3)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Query1 [COLOR=blue]VALUES[/color] (225, 1)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Query1 [COLOR=blue]VALUES[/color] (226, 2)



[COLOR=blue]SELECT[/color] Tbl1.Id, Tbl1.Amt, Tbl1.amt_type, Tbl1.Trans_Type
[COLOR=blue]FROM[/color] ([COLOR=blue]SELECT[/color] *, DENSE_RANK() [COLOR=blue]OVER[/color] ([COLOR=#FF00FF]PARTITION[/color] [COLOR=blue]BY[/color] Id [COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] AMT) [COLOR=blue]AS[/color] Dnst
             [COLOR=blue]FROM[/color] @Table1 [COLOR=blue]As[/color] T1) Tbl1
[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] @Query1 Query1 [COLOR=blue]ON[/color] Tbl1.ID = Query1.ID AND Tbl1.Dnst <= Query1.Audit

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[bborissov]

Sorry, it should be ROW_NUMBER() not DENSE_RANK(). I completely forgot that both RANK function return the same for the same value in the ORDER BY clause:

[COLOR=blue]DECLARE[/color] @Table1 [COLOR=blue]TABLE[/color] (Id [COLOR=blue]int[/color], Amt [COLOR=blue]Numeric[/color](10,2), amt_type [COLOR=blue]varchar[/color](200), Trans_Type [COLOR=blue]varchar[/color](200))
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Table1 [COLOR=blue]VALUES[/color] (223,1.50,[COLOR=red]'cash'[/color],[COLOR=red]'credit'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Table1 [COLOR=blue]VALUES[/color] (223,2.03,[COLOR=red]'visa'[/color],[COLOR=red]'credit'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Table1 [COLOR=blue]VALUES[/color] (223,2.03,[COLOR=red]'TST'[/color],[COLOR=red]'credit'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Table1 [COLOR=blue]VALUES[/color] (223,2.30,[COLOR=red]'MC'[/color],[COLOR=red]'paytogo'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Table1 [COLOR=blue]VALUES[/color] (234,0.50,[COLOR=red]'TC'[/color],[COLOR=red]'paytogo'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Table1 [COLOR=blue]VALUES[/color] (234,1.75,[COLOR=red]'cash'[/color],[COLOR=red]'credit'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Table1 [COLOR=blue]VALUES[/color] (234,0.90,[COLOR=red]'visa'[/color],[COLOR=red]'credit'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Table1 [COLOR=blue]VALUES[/color] (234,5.30,[COLOR=red]'visa'[/color],[COLOR=red]'credit'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Table1 [COLOR=blue]VALUES[/color] (234,1.25,[COLOR=red]'TC'[/color],[COLOR=red]'paytogo'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Table1 [COLOR=blue]VALUES[/color] (225,1.35,[COLOR=red]'MC'[/color],[COLOR=red]'paytogo'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Table1 [COLOR=blue]VALUES[/color] (225,2.24,[COLOR=red]'visa'[/color],[COLOR=red]'credit'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Table1 [COLOR=blue]VALUES[/color] (225,3.35,[COLOR=red]'MC'[/color],[COLOR=red]'paytogo'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Table1 [COLOR=blue]VALUES[/color] (226,2.17,[COLOR=red]'visa'[/color],[COLOR=red]'credit'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Table1 [COLOR=blue]VALUES[/color] (226,1.15,[COLOR=red]'TC'[/color],[COLOR=red]'paytogo'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Table1 [COLOR=blue]VALUES[/color] (226,1.50,[COLOR=red]'TC'[/color],[COLOR=red]'paytogo'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Table1 [COLOR=blue]VALUES[/color] (226,1.60,[COLOR=red]'visa'[/color],[COLOR=red]'credit'[/color])

[COLOR=blue]DECLARE[/color] @Query1 [COLOR=blue]TABLE[/color] (Id [COLOR=blue]int[/color], [COLOR=#FF00FF]Audit[/color] [COLOR=blue]int[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Query1 [COLOR=blue]VALUES[/color] (223, 2)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Query1 [COLOR=blue]VALUES[/color] (234, 3)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Query1 [COLOR=blue]VALUES[/color] (225, 1)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Query1 [COLOR=blue]VALUES[/color] (226, 2)



[COLOR=blue]SELECT[/color] Tbl1.Id, Tbl1.Amt, Tbl1.amt_type, Tbl1.Trans_Type, Dnst
[COLOR=blue]FROM[/color] ([COLOR=blue]SELECT[/color] *, ROW_NUMBER() [COLOR=blue]OVER[/color] ([COLOR=#FF00FF]PARTITION[/color] [COLOR=blue]BY[/color] Id [COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] AMT) [COLOR=blue]AS[/color] Dnst
             [COLOR=blue]FROM[/color] @Table1 [COLOR=blue]As[/color] T1) Tbl1
[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] @Query1 Query1 [COLOR=blue]ON[/color] Tbl1.ID = Query1.ID AND Tbl1.Dnst <= Query1.Audit

As you see I added new record for Id 223 which have the same value in Amt field. With previous example you will get that record too.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[mary1994]

Thank you very much for your help. Let me apology for not providing you enough information. I will not be using sql application. The project is access and the result of this query will be displayed within a form. I do not need the insert section or the form, just the correct query syntax to pull the correct result. I do not need the insert syntax that is because the query is a make query...
----------------------
This is what i am trying to do.
I would like a sql statement or know how to create a query that will look at
column Audit of table2 and pull that amount of records of table1. The result
should be listed by the top Amt for the records based on query1.Audit.

The query i had posted above work well just that it skips record on the production database. I have attached that database so you can take a look at it.
Again. i really appreciate your help and please let me know if you are able right the sql for access. if it is not possible in access i and use Teradata to run the query.

Thanks a million and sorry for the confusion.

I have uploaded a close sample of the production database. As you will see when you run the make table. Record for ID #1 is missing even though there is one audit record for ID#1. The result should have able 621 but it only produce 527 records.
Here is the link for that database

http://itismystory.com/upload/

 

[bborissov]

Mary,
The INSERT statements here is just to prepare data for testing, not for you.
Try this:

[COLOR=blue]SELECT[/color] Tbl1.Id, Tbl1.Amt, Tbl1.amt_type, Tbl1.Trans_Type, Dnst
[COLOR=blue]FROM[/color] ([COLOR=blue]SELECT[/color] *, ROW_NUMBER() [COLOR=blue]OVER[/color] ([COLOR=#FF00FF]PARTITION[/color] [COLOR=blue]BY[/color] Id [COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] AMT) [COLOR=blue]AS[/color] Dnst
             [COLOR=blue]FROM[/color] @Table1 [COLOR=blue]As[/color] T1) Tbl1
[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] @Query1 Query1 [COLOR=blue]ON[/color] Tbl1.ID = Query1.ID AND Tbl1.Dnst <= Query1.Audit

And replace each @Table1 and @Query1 with the real name of your tables.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[bborissov]

MDB???????????????????????????
Isn't that ACCESS Database??
You should ask in ACCESS forum, not in SQL Server one.
Sorry, if it is ACCESS I can't be in much help.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[mary1994]

Thanks. But i should be able to run it within the sql view in access. Just that access sql does not recognize some of the syntax. If you can write it for teradata sql i will gladly use it. I have tried so many access forums with no help.
YOUR HELP IS OF GREAT VALUE TO ME and if you can write it to work on teradata that will be great. Thanks in advance

 

[bborissov]

Sorry, I have no Access installed and I'm not familiar with ACCESS SQL Engine :-(
I know that this syntax will be not appreciated in Access because this is just a SQL Server syntax and not only that this is SQL Server 2005 or 2008 syntax
Is there in ACCESS some way to return a consequential record number in some query?

I mean, no matter if you added records in ACCESS table that way:
INSERT INTO @Table1 VALUES (223,1.50,'cash','credit')
INSERT INTO @Table1 VALUES (223,2.03,'TST','credit')
INSERT INTO @Table1 VALUES (234,0.50,'TC','paytogo')
INSERT INTO @Table1 VALUES (234,1.75,'cash','credit')
INSERT INTO @Table1 VALUES (234,0.90,'visa','credit')
INSERT INTO @Table1 VALUES (234,5.30,'visa','credit')
INSERT INTO @Table1 VALUES (223,2.03,'visa','credit')
INSERT INTO @Table1 VALUES (234,1.25,'TC','paytogo')
INSERT INTO @Table1 VALUES (225,1.35,'MC','paytogo')
INSERT INTO @Table1 VALUES (223,2.30,'MC','paytogo')
INSERT INTO @Table1 VALUES (225,2.24,'visa','credit')
INSERT INTO @Table1 VALUES (225,3.35,'MC','paytogo')
INSERT INTO @Table1 VALUES (226,2.17,'visa','credit')
INSERT INTO @Table1 VALUES (226,1.15,'TC','paytogo')
INSERT INTO @Table1 VALUES (226,1.50,'TC','paytogo')
INSERT INTO @Table1 VALUES (226,1.60,'visa','credit')

Whn you issue some query ordering records by ID to return you:
[tt]
Id Record Number
------------------
223 1
223 2
223 3
223 4
225 5
225 6
225 7
226 8
226 9
226 10
226 11
234 12
234 13
234 14
234 15
234 16
[/tt]



Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[mary1994]

No. this is will work in access and teradata

INSERT INTO Promotion
(EmpName, EmpNo, DeptNo, Sex, DOB)
VALUES ('John Smith', 10001, 876, 'M', 560231);

 

[bborissov]

Can you create a query that will return all records ordered by EmpNo and has an additional column that is incrementing by one for each record returned?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[mary1994]

Thank you so much Borislav. I think i move all of the tables related to this project to Teradata so i can use your sql statement. It works well in sql server, way faster than access. Hope i get lucky again in getting your help if needed.
YOU ARE GREAT................

 

[ESquared]

Without a column in table1 (your transactions table) that can provide some kind of ordering, such as a date or transaction number, you can NOT trust that you are pulling records in any particular order. That is, if you're looking for the "last 2 transactions for ID 223" this is meaningless unless you can somehow order the transactions. Simply getting 2 transactions of 3 is no problem, but the LAST 2 requires some way to order.

 

[mary1994]

ESquared,i am not that good in SQL. bborissov query works great. My be i am not clear in my explaination. but thanks a million

 

[ESquared]

look at bborissov's code: "ORDER BY AMT"

To order rows, you have to specify a column to order by. You should carefully examine whether ordering by the amount will fulfill the business rules of your company and your data.