Filtering Records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Filtering Records

Author

Topic

DB Analyst
Starting Member

8 Posts

Posted - 2011-03-23 : 10:42:58

Hi everyone,

Could someone assist me with developing a query that filters records as part of the query process? This is a query that will be written in Oracle, but I wanted to see if the logic/syntax would be similar in SQL Server.

I have a table with the following fields:

txn_source
txn_amt
txn_id
FLAG

NOTE: TXN_source is a number shared by 2 distinct TXN_IDs. The transaction_id's are all unique. (eg: a Source # 105 is found for both Transaction_IDs 994 and 995.)

I would like to set-up a query that does the following procedures, in order:

- first, select the pairs of transaction_id's for every source
- then, for each pair, getting the lower of the two transaction_amounts
- finally, if the amounts are equal, get the lower transaction_id

NOTE: For the purposes of this exercise, I am not able to set-up any temporary tables.

************************

Here is the SQL used to create my table:

CREATE TABLE [dbo].[FLAG_Practice](
[TXN_SOURCE] [nchar](40) NULL,
[TXN_AMT] [money] NULL,
[TXN_ID] [int] NULL,
[FLAG] [nchar](10) NULL
) ON [PRIMARY]

GO

************************

Here is the data in the table:

insert into dbo.FLAG_Practice
VALUES ('105',150,'994',0),
('105',40,'995',1),
('106',55,'996',1),
('106',55,'997',0),
('107',60,'998',0),
('107', 35,'999',1)

As you can see, only one transaction out of the pair has the FLAG marked as '1'. This is because out of each txn_source, it is either the lower of the transaction amts, or the lower txn_id if the amounts are equal.

************************

How would I write a query that retrieves Transactions 995, 996, and 999 using the logic I specified? Any help would be greatly appreciated. Thanks!

(Please note that for the actual table, I'm dealing with millions of records that don't have the FLAG field filled out. But, just for this example table, it's easy to eye-ball what should be flagged.)

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-23 : 11:10:49

Show us what you have so far and we'll help you figure the rest out. Few people here will actually do homework for you.

Jim

Everyday I learn something that somebody else already knew

DB Analyst
Starting Member

8 Posts

Posted - 2011-03-23 : 11:21:49

Here is what I have so far:

select dt.txn_source, dt.txn_amt, dt.txn_id
from dbo.[FLAG_Practice] dt
join

(Select txn_source,COUNT(txn_id) AS txn_ct
from dbo.[FLAG_Practice]
GROUP by txn_source
HAVING COUNT(txn_id)>1) a1

ON dt.TXN_SOURCE = a1.txn_source

join

(SELECT txn_source,MIN(txn_amt) AS txn_amt
FROM dbo.[FLAG_Practice]
GROUP BY txn_source) a2

ON dt.TXN_SOURCE = a2.TXN_SOURCE

join

(SELECT txn_source, MIN(txn_id) AS txn_id
FROM dbo.[FLAG_Practice]
GROUP BY txn_source) a3

ON dt.TXN_SOURCE = a3.TXN_SOURCE

---The first SELECT is what I want to see as the output.
---Second SELECT finds the pairs of transactions.
---Third SELECT finds the lower transaction amount. (If amts are equal, then the transactions would not be filtered through this step, correct?)
---Fourth SELECT finds the lower transaction id.

The result of this is that it simply shows me all the transactions in the table. Let me know if what part of my logic needs reworking.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-23 : 13:07:35

SELECT *
FROM
(

select f.txn_source,f.txn_amt,f.txn_id
,[Flag] = RANK() over(partition by f.txn_source order by txn_amt asc,txn_id asc)

from #flag_practice as f
) t

WHERE t.[flag] = 1

Jim

Everyday I learn something that somebody else already knew

DB Analyst
Starting Member

8 Posts

Posted - 2011-03-23 : 20:21:22

Thanks, Jim. It's now working.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources