|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
TOP and UNIONSELECT TOP 1 WITH TIES mediaID,count(mediaID)as sumMedia FROM tblMedia GROUP BY mediaID ORDER BY sumMedia DESC This query returns 0 mediaID's: SELECT TOP 1 WITH TIES mediaID,count(mediaID)as sumMedia FROM tblOrders GROUP BY mediaID ORDER BY sumMedia DESC This query returns only 1 mediaID instead of 2: SELECT TK.* FROM (SELECT TOP 1 WITH TIES mediaID,count(mediaID)as sumMedia FROM tblMedia GROUP BY mediaID ORDER BY sumMedia DESC UNION ALL SELECT TOP 1 WITH TIES mediaID,count(mediaID)as sumMedia FROM tblOrders GROUP BY mediaID ORDER BY sumMedia DESC)as Tk Does anybody know why? If I write like this: SELECT TK.* FROM( SELECT T1.* FROM( SELECT TOP 1 WITH TIES mediaID,count(mediaID)as sumMedia FROM tblMedia GROUP BY mediaID ORDER BY sumMedia DESC)as T1 UNION ALL SELECT T1.* FROM( SELECT TOP 1 WITH TIES mediaID,count(mediaID)as sumMedia FROM tblOrders GROUP BY mediaID ORDER BY sumMedia DESC)as T1)as Tk then I get 2 mediaIDs,which is the result I want. But I don't understand why first query returns only one. Is there some explanation? Thanks,Simon I think you cannot use Order by in Union All
To do that you need to make that as a table thats what you did in the second case Madhivanan > [...] I was not able to reproduce the problem using some similar tables on> This query returns only 1 mediaID instead of 2: > [...] SQL 2000 SP4 (I got the expected result: 2 rows). What version of SQL Server are you using ? Can you post some DDL and sample data so we can reproduce the problem ? Razvan I found it.
It's about version. Working version: Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: ) Non working version: Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4) Which service pack is on non working version? I guess that Service Pack 4 means windows service pack? Regards, Simon Show quote "Razvan Socol" <rso***@gmail.com> wrote in message news:1132828787.973501.151890@g43g2000cwa.googlegroups.com... >> [...] >> This query returns only 1 mediaID instead of 2: >> [...] > > I was not able to reproduce the problem using some similar tables on > SQL 2000 SP4 (I got the expected result: 2 rows). What version of SQL > Server are you using ? Can you post some DDL and sample data so we can > reproduce the problem ? > > Razvan > > Which service pack is on non working version?
http://www.aspfaq.com/show.asp?id=2160
> I guess that Service Pack 4 means windows service pack? Correct.-- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Simon" <simon.zu***@studio-moderna.com> wrote in message news:OAFUwzP8FHA.3752@tk2msftngp13.phx.gbl... >I found it. > > It's about version. > > Working version: > > Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) > May 3 2005 23:18:38 > Copyright (c) 1988-2003 Microsoft Corporation > Enterprise Edition on Windows NT 5.2 (Build 3790: ) > > Non working version: > > Microsoft SQL Server 2000 - 8.00.760 (Intel X86) > Dec 17 2002 14:22:05 > Copyright (c) 1988-2003 Microsoft Corporation > Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4) > > Which service pack is on non working version? I guess that Service Pack 4 means windows service > pack? > > Regards, > Simon > > "Razvan Socol" <rso***@gmail.com> wrote in message > news:1132828787.973501.151890@g43g2000cwa.googlegroups.com... >>> [...] >>> This query returns only 1 mediaID instead of 2: >>> [...] >> >> I was not able to reproduce the problem using some similar tables on >> SQL 2000 SP4 (I got the expected result: 2 rows). What version of SQL >> Server are you using ? Can you post some DDL and sample data so we can >> reproduce the problem ? >> >> Razvan >> > >  |
|||||||||||||||||||||||
Other interesting topics