If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Union Query Not Working Properly
I am trying to use a Union Query to pull the top 2 records for each group from
the same table. It works for the very first part (it pulls the 2 highest numbers) but it fails in the other part of the statement...the part that fails does give 2 records, but they are not the highest numbers. Table name: growth Fields: [mem]...field for name of members, (text) [por].....field for amount (integer) [dato]....field for date Below if the SQL statement...why is the first part working and the other part not? SELECT TOP 2 growth.mem, growth.por, growth.dato FROM growth WHERE (((growth.por)="dw")) ORDER BY growth.mem DESC Union SELECT TOP 2 growth.mem, growth.por, growth.dato FROM growth WHERE (((growth.por)="jj")) ORDER BY growth.mem DESC; |
#2
|
|||
|
|||
Union Query Not Working Properly
The second ORDER BY Clause applies to all the rows that
have been selected by the Union. It doesn't apply to the second SELECT statement, i.e. the second part of the Union. Try (NOT tested): SELECT TOP 2 growth.mem, growth.por, growth.dato FROM growth WHERE (((growth.por)="dw")) ORDER BY growth.mem DESC Union SELECT VT.mem, VT.por, VT.dato FROM ( SELECT TOP 2 growth.mem, growth.por, growth.dato FROM growth WHERE (((growth.por)="jj")) ORDER BY growth.mem DESC ) As VT If you use A97, you may need a different syntax for the SubQuery HTH Van T. Dinh MVP (Access) -----Original Message----- I am trying to use a Union Query to pull the top 2 records for each group from the same table. It works for the very first part (it pulls the 2 highest numbers) but it fails in the other part of the statement...the part that fails does give 2 records, but they are not the highest numbers. Table name: growth Fields: [mem]...field for name of members, (text) [por].....field for amount (integer) [dato]....field for date Below if the SQL statement...why is the first part working and the other part not? SELECT TOP 2 growth.mem, growth.por, growth.dato FROM growth WHERE (((growth.por)="dw")) ORDER BY growth.mem DESC Union SELECT TOP 2 growth.mem, growth.por, growth.dato FROM growth WHERE (((growth.por)="jj")) ORDER BY growth.mem DESC; . |
Thread Tools | |
Display Modes | |
|
|