Union All - Not the usual - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Union All - Not the usual

Author

Topic

duleepN
Starting Member

12 Posts

Posted - 2008-08-27 : 11:56:51

All;
I have a similar query as below
-------------------------------
Select A,B,C,D
From AA
Inner join BB on AA.A=BB.E
Left Join CC on AA.B=(Select top 1 from CC order by K DESC)

WHERE .......

UNION ALL

Select A,B,C,D
From AA
Inner join BB on AA.A=BB.E

WHERE .......

-----------------------------------

Above worls fine if i dont have the order by.....
But i need the order by pther wise join does not bring correct data.

HOW CAN I ACHEIVE THIS

Please HELP!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-27 : 12:00:34

How does not having ORDER BY affecting the result in your above query ?

KH
[spoiler]Time is always against us[/spoiler]

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-27 : 12:07:22

order by where? in the select top 1 ??

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-08-27 : 12:08:20

Select Something From
(
Select A,B,C,D
...
UNION ALL
Select A,B,C,D
...
) as MustHaveANameElseSyntaxError
Order By Whatever

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 12:55:46

quote:
Originally posted by duleepN

All;
I have a similar query as below
-------------------------------
Select A,B,C,D
From AA
Inner join BB on AA.A=BB.E
Left Join CC on AA.B=(Select top 1 from CC order by K DESC)

WHERE .......

UNION ALL

Select A,B,C,D
From AA
Inner join BB on AA.A=BB.E

WHERE .......

-----------------------------------

Above worls fine if i dont have the order by.....
But i need the order by pther wise join does not bring correct data.

HOW CAN I ACHEIVE THIS

Please HELP!!

Order By has nothing to do with join part. Didnt understand your scenario. Please illustrate with some sample data.

duleepN
Starting Member

12 Posts

Posted - 2008-08-28 : 06:43:00

Order by is in the select top 1.
Why i need that is there are more than one relevant entry in that table so i need to order it by the date and get the most recent row.
Appreciate your help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-28 : 07:17:49

and what has that to do with your join? why you think its affecting join results?

duleepN
Starting Member

12 Posts

Posted - 2008-08-28 : 07:25:47

i thought if i do that it will give me the most recent row (as i am doing select top 1)in my joint. if i dont it gives me what ever on top.(as i mentioned there is more that one matching row in that table)
Correct me if im wrong.

duleepN
Starting Member

12 Posts

Posted - 2008-08-28 : 07:49:03

This is the error i get
ORDER BY items must appear in the select list if the statement contains a UNION operator

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-28 : 07:52:28

quote:
Originally posted by duleepN

This is the error i get
ORDER BY items must appear in the select list if the statement contains a UNION operator

this can be avoided by putting a derived table with union query and doing order by outside the derived table.Like what bjoerns has shown at 08/27/2008 : 12:08:20

duleepN
Starting Member

12 Posts

Posted - 2008-08-28 : 09:47:05

OK! Let me Rephrase my question
-------------------------------
Table Calls
-----------
Call_ref
........
123
124
125
126

Table Invoice
-------------
Call_ref Amount Date
........ ....... ......
123 £20 01/08/2008
123 £21 02/08/2008
123 £22 03/08/2008
124 £80 01/08/2008
124 £81 02/08/2008
126 £40 04/08/2008

Assume there are two table. Calls and Invoice
What i want is Only the most recent invoice amount

123 £22 03/08/2008
124 £81 02/08/2008
126 £40 04/08/2008

I want other fields from the calls table so it has to be a JOIN
Also this is part of a UNION ALL query.

Hope i make sense now.....
Thanks#

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-28 : 09:55:51

[code]SELECT c.fields..,i.Amount,i.Date
FROM Call c
INNER JOIN Invoice i
ON i.Call_ref=c.Call_ref
INNER JOIN (SELECT Call_ref,MAX(Date) AS MaxDate
FROM Invoice
GROUP BY Call_ref)i1
ON i1.Call_ref=i.Call_ref
AND i1.MaxDate=i.Date[/code]

or

[code]SELECT t.*
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY c.Call_ref ORDER BY i.Date DESC) AS Seq,
c.fields..,i.Amount,i.Date
FROM Call c
INNER JOIN Invoice i
ON i.Call_ref=c.Call_ref
)t
WHERE t.Seq=1[/code]

duleepN
Starting Member

12 Posts

Posted - 2008-08-28 : 12:02:58

Thanks Visakh16. It works like a dream!

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-08-28 : 14:52:37

just a quick question, Is there an equivalent for MERGE in sql server 2005??

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-08-28 : 14:54:06