How to use UNION ALL in SQL query.

Bineesh Viswanath

9y941

Sir,

I need your help in below query.

I need to use order by and UNION ALL in query given below in order to get 1000 rows in a single execution.

select strCustomerID, strDate, strUser, strAction from

( select top 1 *

from tblFollowupdetail where strCustomerID='AB7356'

and strDate between convert(datetime,'01/12/2016',103)

and convert(datetime,'15/12/2016',103)

order by strDate desc ) last

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Manas Mohapatra9y ago
Regarding duplicate records, either you can use Distinct or UNION only in SQL query.
ali tuncer9y ago
I think first you need to group by customer Id and get maxdate, so it will be latest record for each customer..then you can join this table to tblFollowupdetail to get other columns..
select strCustomerID,max(strDate) as last
from tblFollowupdetail
--here you can write the criteria
group by strCustomerID
Nitin Sontakke9y ago
The best approach is to show some sample output as a screen shot. And then tell us which lines are duplicate and you don't expect them to be seen. Of course we would like to know criteria (the basis) so that we can apply that.
Bineesh Viswanath9y ago
The result is coming.. but have duplicated value.
Nitin Sontakke9y ago
Got it.

This is the reason we expect that someone seeking answer should tell us what is expected ultimately. Because the path one may have in his/her mind itself might be wrong one.

All you have do it giver customer ids in IN clause. As shown below:

select strCustomerID, strDate, strUser, strAction
from tblFollowupdetail
where strCustomerID in ('AB8120', 'AB9058') -- add as many in comma separate values
and strDate between convert(datetime,'01/12/2016',103)
and convert(datetime,'15/12/2016',103)
order by strDate desc

Where is this code? Is it in a stored procedure? If yes, there is yet another better approach available.
Bineesh Viswanath9y ago
Actually I given only one customerID.

Again for your sake, I here going to give two sql query joined by union all.

like this I have to execute 1000 customerID and want all that in a single result.
select strCustomerID, strDate, strUser, strAction

from (

select top 1 *

from tblFollowupdetail where strCustomerID='AB9058'

and strDate between convert(datetime,'01/12/2016',103)

and convert(datetime,'15/12/2016',103)

order by strDate desc) last

union all

select strCustomerID, strDate, strUser, strAction

from (

select top 1 *

from tblFollowupdetail where strCustomerID='AB8120'

and strDate between convert(datetime,'01/12/2016',103)

and convert(datetime,'15/12/2016',103)

order by strDate desc) last
Manas Mohapatra9y ago
yes, as Nitin said, how UnionAll came into picture. Describe your requirement more to understand it properly.
Nitin Sontakke9y ago
Somehow things just don't add up. Please state your exact final requirement. Your query inside the () is selecting just 1 record. How do you expect it to return 1000 records? The inner queries cannot have ORDER BY clause. Only the final outer query can have ORDER BY clause.

Where does the UNION ALL come into picture is not quite clear.