How to use UNION ALL in SQL query.

Question

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

Nitin Sontakke · Answer

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.

Manas Mohapatra · Answer

Regarding duplicate records either you can use Distinct or UNION only in SQL query.

ali tuncer · Answer

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 strCustomerIDmax(strDate) as last from tblFollowupdetail --here you can write the criteria group by strCustomerID

Nitin Sontakke · Answer

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 VP · Answer

The result is coming.. but have duplicated value.

Nitin Sontakke · Answer

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 VP · Answer

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 Mohapatra · Answer

yes as Nitin said how UnionAll came into picture. Describe your requirement more to understand it properly.