Selecting rows based on combination of 2 columns f

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

SQL Server 2008 Forums

Transact-SQL (2008)

Selecting rows based on combination of 2 columns f

Author

Topic

inquisitive123
Starting Member

11 Posts

Posted - 2010-10-05 : 06:23:19

Hi All,

I am trying to select all rows from a table with trade info based on the same combination of trade execution time and trade execution price to create report in SSRS

Requirement : Select all rows with same Exec time and same exec price
For eg:
Execution time : 9:00:01 ExecPrice 90.00
Execution time : 9:00:01 ExecPrice 90.00
Execution time : 10:00:01 ExecPrice 80.00
Execution time : 10:00:01 ExecPrice 80.00

I tried with
SELECT ExecutionTime, ClTradePrice
FROM core.tblTsTrade
WHERE (DayCode = 20091231)
GROUP BY Executiontime, ClTradePrice
HAVING (COUNT(*) > 1)

but this does not work since I need to select more columns from the same table, also perform joins with atleast 5 tables.

Could somebody pls help with this.
Thank You.

sai

Sachin.Nand

2937 Posts

Posted - 2010-10-05 : 06:34:46

What are those other tables & columns & show us how they are related?

PBUH

inquisitive123
Starting Member

11 Posts

Posted - 2010-10-05 : 18:10:16

It is connected to a order table with an order id, a trade detail table with the daycode,trade status table with the trade status,Security table with security code etc

But the main issue is selecting records from the same table with same exec time and price

sai

Sachin.Nand

2937 Posts

Posted - 2010-10-06 : 02:52:24

[code]
;with cte
as
(
select * from
(
SELECT ExecutionTime, ClTradePrice,'other columns which you need'
row_number()over(partition by Executiontime,ClTradePrice order by Executiontime)rid
FROM core.tblTsTrade
)t where rid=1
)

select * from cte
you can join it with other 5 tables
[/code]

PBUH

inquisitive123
Starting Member

11 Posts

Posted - 2010-10-06 : 21:13:24

Thanks a lot for that!!It worked really well....

However there is only one problem I m facing now.......The result set I got was

rid Exectime TradePrice
--- -------- ----------

1 12:25:11 82
2 12:25:11 82
1 12:25:11 82.15
1 12:25:11 82.3
1 12:25:11 82.5
1 12:25:11 82.55
1 12:25:11 1570
1 12:25:12 1082.9
1 12:25:13 82
2 12:25:13 82
1 12:55:47 1471.1
2 12:55:47 1471.1
3 12:55:47 1471.1

I want to select all the records with duplicate values,so in this case it will be the ones with rid 1,2 and 3 for the ones with max rid>1

If I use rid>1 or rid<>1, I am not able to get the rows with rid 1 for duplicate records.......
with cte
as
(
select * from
(
SELECT ExecutionDatetime, ClTradePrice,ClTradeId,InstrumentCode,ExchangeId,ClOrderId,ClBuySell,cdbid,
core.tblTsTrade.DayCode, core.tblTsTrade.ClQuantity, CONVERT(varchar(10), core.tblTsTrade.ExecutionDatetime, 108) AS ExecutionTime,
row_number()over(partition by ExecutionDatetime ,ClTradePrice order by ExecutionDatetime)rid
FROM core.tblTsTrade where DayCode=20091231 AND ExchangeID in ('NSE','BOM')AND ClTradePrice IS NOT NULL
)t where rid>1
)
select * from cte

How do I solve that issue pls??

sai

Sachin.Nand

2937 Posts

Posted - 2010-10-07 : 01:52:28

[code]
declare @tbl table(id int,tm varchar(10),val decimal(10,2))
insert into @tbl
select 1, '12:25:11',82 union all
select 2, '12:25:11', 82 union all
select 1,'12:25:11', 82.15 union all
select 1 ,'12:25:11', 82.3 union all
select 1 ,'12:25:11', 82.5 union all
select 1 ,'12:25:11', 82.55 union all
select 1 ,'12:25:11', 1570 union all
select 1 ,'12:25:12', 1082.9 union all
select 1 ,'12:25:13', 82 union all
select 2 ,'12:25:13', 82 union all
select 1 ,'12:55:47', 1471.1 union all
select 2 ,'12:55:47', 1471.1 union all
select 3 ,'12:55:47', 1471.1

select * from
(
select *,count(id)over(partition by tm,val)as cnt from @tbl
)t where cnt>1
[/code]

PBUH

inquisitive123
Starting Member

11 Posts

Posted - 2010-10-08 : 02:36:58

Thank you very much!

Sachin.Nand

2937 Posts

Posted - 2010-10-08 : 03:09:39