tanj1035
asked on
SQL, the seqnum order became different after table join
Hi , Expert,
After I join #a and "makemydeal_com..dealrespo
I tried left join, right join, inner join . Also, I tried a.dealresponseid desc, a.timestamp desc and a.seqnum desc. It still returns the wrong seqnum order. Thank you.
After I join #a and "makemydeal_com..dealrespo
I tried left join, right join, inner join . Also, I tried a.dealresponseid desc, a.timestamp desc and a.seqnum desc. It still returns the wrong seqnum order. Thank you.
# A
declare @StartDate date
declare @EndDate date
set @StartDate ='2015-06-1'
set @EndDate ='2015-7-14'
select dealid, seqnum, timestamp, dealresponseid,
case when ShopperResponseStatus in ('Sent','Accepted','Archived') then 'Shopper' else 'Dealer' end as Actor
into #a
from mmd_feed..dealresponsesequence
where dealid=30357
order by seqnum asc
#B
select a.*, dr.sentbydealercontactid
into #b
from #a a
left join makemydeal_com..dealresponse2 dr on a.dealid=dr.dealid and a.dealresponseid=dr.dealresponseid
order by a.dealresponseid desc
You want it ASC order why are you using desc ?
#B
select a.*, dr.sentbydealercontactid
into #b
from #a a
left join makemydeal_com..dealrespon
order by a.seqnum desc
Or
select * into #b from (
select a.*, dr.sentbydealercontactid
from #a a
left join makemydeal_com..dealrespon
) T order by seqnum ASC
#B
select a.*, dr.sentbydealercontactid
into #b
from #a a
left join makemydeal_com..dealrespon
order by a.seqnum desc
Or
select * into #b from (
select a.*, dr.sentbydealercontactid
from #a a
left join makemydeal_com..dealrespon
) T order by seqnum ASC
ASKER
Hi Deepark, Sten5an, Sorry, desc is a type error.
I tried both of your quries. but the seqnum order still returns 3,1,2,4,5,6. Do you have any suggestions? Thanks.
I tried both of your quries. but the seqnum order still returns 3,1,2,4,5,6. Do you have any suggestions? Thanks.
Did you tried this.
select * into #b from (
select a.*, dr.sentbydealercontactid
from #a a
left join makemydeal_com..dealresponse2 dr on a.dealid=dr.dealid and a.dealresponseid=dr.dealresponseid
) T order by seqnum ASC ASKER
Yes, I did, Deepak, it returns 3,1,2,4,5,6.
Yup, haven't read your post carfullly enough. Your second query is a tabel creation query.
Thus you have not posted the query which retrieves the data. Without ORDER BY in the query which retrieves the data, there is no guarantee, that the data is returned in any predictible order.
Thus you have not posted the query which retrieves the data. Without ORDER BY in the query which retrieves the data, there is no guarantee, that the data is returned in any predictible order.
ASKER
hi Ste5an,
Please correct me if I understand correctly. Do you mean I should change the query b with a correct syntax "asc", like this
select a.*, dr.sentbydealercontactid
into #b
from #a a
left join makemydeal_com..dealrespon
order by seqnum asc
If so, #b still returns me a wrong seqnum order
Please correct me if I understand correctly. Do you mean I should change the query b with a correct syntax "asc", like this
select a.*, dr.sentbydealercontactid
into #b
from #a a
left join makemydeal_com..dealrespon
order by seqnum asc
If so, #b still returns me a wrong seqnum order
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes, you are right. Thx for sharing.
Open in new window