Get data from one select and get max records

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 2005 Forums

Transact-SQL (2005)

Get data from one select and get max records

Author

Topic

tpavan7329
Starting Member

18 Posts

Posted - 2011-03-08 : 17:41:49

I want to combine both the queries below into one select statement without using Temp table and insert them in Table A.

First Select gets all the history for a phoneid and its id

For example for a phone ‘19999999999’, if there are two id’s 11494462 and 11494465, second query will pick only max of id record for a phone.

declare @startDate datetime
declare @endDate datetime
set @startDate= getdate() - 1
set @endDate= getdate()

--select @startdate, @enddate

--AFP records
select
m.phoneid as afp_id
,m.destination as afp_destination
,mh.id as afp_mhid
,mh.statusCode as afp_statusCode
,mh.reasoncode as afp_reasonCode
,mh.statusText as afp_statusText
,mh.aStatusCode as afp_aStatusCode
,mh.aReasonCode as afp_aReasonCode
,mh.aggregatorStatusText as afp_aStatusText
,mh.created as afp_CreationDate
into #temp_afp_all_history
from [Mhistory] mh,messagess] m with (nolock)
where mh.msgTableId=m.id and responsetype=7 and m.created between @startDate and @endDate

--AFP records with most recent messages per id
select afp.*
into #temp_afp_all
from #temp_afp_all_history afp
inner join (select afp_phoneid,max(afp_mhid) as afp_mhid from #temp_afp_all_history group by afp_phoneid) afp_last
on afp.afp_mhid=afp_last.afp_mhid

T Pavan Kumar

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-08 : 21:05:32

[code]
; with data as
(
select
m.phoneid as afp_id
,m.destination as afp_destination
,mh.id as afp_mhid
,mh.statusCode as afp_statusCode
,mh.reasoncode as afp_reasonCode
,mh.statusText as afp_statusText
,mh.aStatusCode as afp_aStatusCode
,mh.aReasonCode as afp_aReasonCode
,mh.aggregatorStatusText as afp_aStatusText
,mh.created as afp_CreationDate,
row_no = row_number() over (partition by afp_phoneid order by afp_mhid desc)
from [Mhistory] mh,messagess] m with (nolock)
where mh.msgTableId=m.id and responsetype=7
and m.created between @startDate and @endDate
)
select *
from data
where row_no = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources