| Author |
Topic |
|
insanepaul
Posting Yak Master
178 Posts |
 Posted - 2008-11-12 : 12:41:30
|
| I'm using a union command to join 2 sets of similar data. It nicely stacks all the most necessary data at the top and the not so necessary data at the bottom. How do I order both sections of the union? I've tried 'order by' but I get errors |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-12 : 12:47:27
|
| you cant use order by on both sides of union. can you illustrate the order you desire with some sample data? |
 |
|
|
insanepaul
Posting Yak Master
178 Posts |
Posted - 2008-11-12 : 12:51:45
|
quote:
Originally posted by visakh16
you cant use order by on both sides of union. can you illustrate the order you desire with some sample data?
I didn't think I could. It's not important though. But you want my data so I'm guessing there is a way around it. Is it a bit complicated? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-12 : 12:55:31
|
quote:
Originally posted by insanepaul
quote:
Originally posted by visakh16
you cant use order by on both sides of union. can you illustrate the order you desire with some sample data?
I didn't think I could. It's not important though. But you want my data so I'm guessing there is a way around it. Is it a bit complicated?
not too complicated. but can you provide a jist of your data. sample data will do. i dont want real data from table. just to know on which field basis you want to order. |
|
|
|
insanepaul
Posting Yak Master
178 Posts |
Posted - 2008-11-12 : 12:59:22
|
quote:
Originally posted by visakh16
quote:
Originally posted by insanepaul
quote:
Originally posted by visakh16
you cant use order by on both sides of union. can you illustrate the order you desire with some sample data?
I didn't think I could. It's not important though. But you want my data so I'm guessing there is a way around it. Is it a bit complicated?
not too complicated. but can you provide a jist of your data. sample data will do. i dont want real data from table. just to know on which field basis you want to order.
Here is the union. There are 5 fields. I would like to order it by sname which is the second field. The top section output is something like:'jklydkfd1f2d3f4dfj'...Tracker...1...a date...a date'sdf1sdf2sdf3sd1f'.....Business...1...a date...a dateand the bottom section is:'sdfs1d2fsad3fsdf'...Sitestuff...0...a date...a date'sdfsdf1sad2f12sdf'...Analysis...0...a date...a dateThere are a total of 155 rows so it would be nice to order each section by the name which is the second column. I use the 3rd column to check a checkbox or not. select strsite_tool.stoolguid, strtool.sname, 1 as publish, strsite_tool.dtmPublishlastsaveddate, strtool.dtmlastsaveddatefrom strtool, strsite_toolwhere strsite_tool.stoolguid = strtool.stoolguid and strsite_tool.ssiteguid = '{b9def02b-33fd-4f61-87bc-3ae3a200504d}'unionselect stoolguid, sname, 0 as publish, dtmlastsaveddate, dtmlastsaveddatefrom strtool where stoolguid not in (select stoolguid from strsite_tool where ssiteguid = '{b9def02b-33fd-4f61-87bc-3ae3a200504d}') |
|
|
|
insanepaul
Posting Yak Master
178 Posts |
Posted - 2008-11-12 : 13:19:27
|
quote:
Originally posted by visakh16
quote:
Originally posted by insanepaul
quote:
Originally posted by visakh16
you cant use order by on both sides of union. can you illustrate the order you desire with some sample data?
I didn't think I could. It's not important though. But you want my data so I'm guessing there is a way around it. Is it a bit complicated?
not too complicated. but can you provide a jist of your data. sample data will do. i dont want real data from table. just to know on which field basis you want to order.
I accidently clicked the reply button before adding anything but I edited it so you may have missed it. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-12 : 13:25:27
|
| Select *from(select strsite_tool.stoolguid, strtool.sname, 1 as publish, strsite_tool.dtmPublishlastsaveddate, strtool.dtmlastsaveddate,1 AS Sectionfrom strtool, strsite_toolwhere strsite_tool.stoolguid = strtool.stoolguid and strsite_tool.ssiteguid = '{b9def02b-33fd-4f61-87bc-3ae3a200504d}'unionselect stoolguid, sname, 0 as publish, dtmlastsaveddate, dtmlastsaveddate,2 from strtool where stoolguid not in (select stoolguid from strsite_tool where ssiteguid = '{b9def02b-33fd-4f61-87bc-3ae3a200504d}'))tOrder By Section,sname |
|
|
|
insanepaul
Posting Yak Master
178 Posts |
Posted - 2008-11-12 : 13:30:25
|
quote:
Originally posted by visakh16
Select *from(select strsite_tool.stoolguid, strtool.sname, 1 as publish, strsite_tool.dtmPublishlastsaveddate, strtool.dtmlastsaveddate,1 AS Sectionfrom strtool, strsite_toolwhere strsite_tool.stoolguid = strtool.stoolguid and strsite_tool.ssiteguid = '{b9def02b-33fd-4f61-87bc-3ae3a200504d}'unionselect stoolguid, sname, 0 as publish, dtmlastsaveddate, dtmlastsaveddate,2 from strtool where stoolguid not in (select stoolguid from strsite_tool where ssiteguid = '{b9def02b-33fd-4f61-87bc-3ae3a200504d}'))tOrder By Section,sname
Wow thanks! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-12 : 13:36:04
|
Cheers  .Hope at least now you found this forum usefulhttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=113701 |
|
|
|
insanepaul
Posting Yak Master
178 Posts |
Posted - 2008-11-13 : 04:19:15
|
quote:
Originally posted by visakh16
Cheers .Hope at least now you found this forum usefulhttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=113701
Yes and very quick response too which is top class. I eventually worked out a way of connecting to the server using the linked server.Actually, can you tell me where in sqlSMS 2005 where I set the connection timeout or better still how to programmatically set the property. I've seen the screen print but just can't find it. I've posted this problem but still can't find it. |
|
|
|
|
Ordering both parts of a union