UNION question? - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

UNION question?

Author

Topic

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2010-11-09 : 05:57:19

Hi,

I have a sql statement like below;

Select field1,field2,field3
from table1
where field2 = 'abc'
UNION
Select field1,field2,field3
from table2
where field2 = 'abc'

I would like to use ORDER BY and TOP( ) for this query.

Thanks in advance.

Best Regards

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-09 : 06:07:07

select top (...) * from
(Select field1,field2,field3
from table1
where field2 = 'abc'
UNION
Select field1,field2,field3
from table2
where field2 = 'abc'

)dt
order by ...

No, you're never too old to Yak'n'Roll if you're too young to die.

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2010-11-09 : 06:10:06

thanks again :)
Bytheway , is there a difference between UNION and UNION ALL???

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-09 : 06:13:16

quote:
Originally posted by raysefo

thanks again :)
Bytheway , is there a difference between UNION and UNION ALL???

Yes.
UNION ALL returns all selected rows.
UNION returns only DISTINCT rows.

No, you're never too old to Yak'n'Roll if you're too young to die.

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2010-11-09 : 06:14:47

thanks

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-11-09 : 06:15:45

quote:
Originally posted by raysefo

thanks again :)
Bytheway , is there a difference between UNION and UNION ALL???

UNION
The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.

UNION ALL
The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.

The difference between Union and Union all is that Union all will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.

A UNION statement effectively does a SELECT DISTINCT on the results set. If you know that all the records returned are unique from your union, use UNION ALL instead, it gives faster results.

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2010-11-09 : 06:22:21

You could use a temporary table as an intermediate e.g.

Select field1,field2,field3
INTO #MyTempTable
from table1
where field2 = 'abc'
UNION
Select field1,field2,field3
from table2
where field2 = 'abc'

then do

Select TOP 20
from #MyTempTable
ORDER BY field1

-----------

I used to think I wasn't a morning person but it never got better in the afternoon

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2010-11-09 : 06:22:58

Dammit I was too slow posting :)

My first thought was what fred has posted however I got a syntax error from this

select top (...) * from
(Select field1,field2,field3
from table1
where field2 = 'abc'
UNION
Select field1,field2,field3
from table2
where field2 = 'abc'

)
order by ...

Spot the difference - for some reason the dt alias is needed. I have no idea why though

-----------

I used to think I wasn't a morning person but it never got better in the afternoon

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2010-11-09 : 06:26:45

How about if the ORDER BY field is something like below,

CONVERT(CHAR(10),Eform_Vekalet.CreationDate,104) +SUBSTRING(CONVERT(varchar,Eform_Vekalet.CreationDate,113),12,9) AS 'Talep_Zamani',

How can i use this in the query below?

select top (...) * from
(Select field1,field2,field3
from table1
where field2 = 'abc'
UNION
Select field1,field2,field3
from table2
where field2 = 'abc'

)dt
order by ...

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2010-11-09 : 06:28:14

I mean the field3 is like this

CONVERT(CHAR(10),Eform_Vekalet.CreationDate,104) +SUBSTRING(CONVERT(varchar,Eform_Vekalet.CreationDate,113),12,9) AS 'Talep_Zamani'

And i would like to order by with field3

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2010-11-09 : 06:46:52

Just put your convert statment in and it will work e.g.

select top (...) * from
(Select field1,field2,CONVERT(CHAR(10),Eform_Vekalet.CreationDate,104) +SUBSTRING(CONVERT(varchar,Eform_Vekalet.CreationDate,113),12,9) AS field3
from table1
where field2 = 'abc'
UNION
Select field1,field2,CONVERT(CHAR(10),Eform_Vekalet.CreationDate,104) +SUBSTRING(CONVERT(varchar,Eform_Vekalet.CreationDate,113),12,9)
from table2
where field2 = 'abc'

)dt
order by CONVERT(CHAR(10),Eform_Vekalet.CreationDate,104) +SUBSTRING(CONVERT(varchar,Eform_Vekalet.CreationDate,113),12,9)

-----------

I used to think I wasn't a morning person but it never got better in the afternoon

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2010-11-09 : 06:51:13

But the problem is CONVERT statments are NOT same.
CONVERT(CHAR(10),table1.CreationDate,104) +SUBSTRING(CONVERT(varchar,table1.CreationDate,113),12,9) AS field3
..
CONVERT(CHAR(10),table2.CreationDate,104) +SUBSTRING(CONVERT(varchar,table2.CreationDate,113),12,9) AS field3

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2010-11-09 : 07:13:55

It doesn't matter. You can put a different convert statement in each of the select statements, I only used one of the convert statements you gave as an example.

What will be important is the datatype produced, these should be the same for corresponding fields (which yours does). The first select statement in your Union will determine the datatype (I think) and the name of the field (hence no alias in my second select statement)

-----------

I used to think I wasn't a morning person but it never got better in the afternoon

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2010-11-09 : 07:17:39

The multi-part identifier "Eform_Vekalet.CreationDate" could not be bound

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-09 : 07:26:33

We need the involved table structure(s).
Without we can't help.

No, you're never too old to Yak'n'Roll if you're too young to die.

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2010-11-09 : 07:36:46

Table1 (field1:varchar,field2:varchar,field3:datetime)
Table2 (field1:varchar,field2:varchar,field3:datetime)

Field names are same. Here is the saple query:

SELECT table1.field1 AS 'Talebi_Yapan',
CONVERT(CHAR(10),table1.field3,104) + SUBSTRING(CONVERT(varchar,table1.field3,113),12,9) AS 'Talep_Zamani',
table1.field2 as 'EForm_Adi',
FROM table1
WHERE table1.field4 = 'abc'
UNION
SELECT table2.field1 AS 'Talebi_Yapan',
CONVERT(CHAR(10),table2.field3,104) + SUBSTRING(CONVERT(varchar,table2.field3,113),12,9) AS 'Talep_Zamani',
table2.field2 as 'EForm_Adi',
FROM table2
WHERE table2.field4 = 'abc'

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-09 : 07:58:27

No.
That has nothing to do with Eform_Vekalet.CreationDate

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources