|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
|
I Apologize if I'm not clear, I'm going to try and explain what I need. This query works fine: On the table "k" there is a display_order column that I would like the k.sentence_name ordered by. If I add vk.display_order in the order by, I have to add it as well in the select but, this screws up using Distinct in the Select? The display_order column basically stores a number value indicating the order of the k.sentence_name is shown to the user in a application, I would also like to order it that way in the return results. SELECT DISTINCT k.kb_Classification_ekey AS [Classification key], c.classification_name AS [Classification Name], vm.menu_name AS [Menu Name], k.sentence_name AS [Child Menu Name] FROM ingres_keyword AS k, ingres_kb_classification AS c, ingres_view_keyword AS vk, ingres_view_menu AS vm WHERE k.kb_classification_ekey=c.ekey And k.ekey=vk.keyword_ekey And vm.ekey=vk.view_menu_parent_ekey And k.kb_classification_ekey In (129,229,279,294,312,313,314,315,320) ORDER BY k.kb_classification_ekey, vm.menu_name, k.sentence_name; thanks gv SELECT k.kb_Classification_ekey AS [Classification key],
c.classification_name AS [Classification Name], vm.menu_name AS [Menu Name], k.sentence_name AS [Child Menu Name] FROM ingres_keyword AS k, ingres_kb_classification AS c, ingres_view_keyword AS vk, ingres_view_menu AS vm WHERE k.kb_classification_ekey=c.ekey And k.ekey=vk.keyword_ekey And vm.ekey=vk.view_menu_parent_ekey And k.kb_classification_ekey In (129,229,279,294,312,313,314,315,320) group by k.kb_Classification_ekey,c.classification_name,vm.menu_name AS [Menu Name], k.sentence_name ORDER BY k.kb_classification_ekey, vm.menu_name, k.sentence_name,max(vk.display_order); -- Show quote-oj "gv" <viat***@musc.edu> wrote in message news:uHNlqBTxFHA.3236@TK2MSFTNGP14.phx.gbl... > Hi all, > > I Apologize if I'm not clear, I'm going to try and explain what I need. > > This query works fine: On the table "k" there is a display_order column > that I would like the k.sentence_name ordered by. > If I add vk.display_order in the order by, I have to add it as well in the > select but, this screws up using Distinct in the Select? > > The display_order column basically stores a number value indicating the > order of the k.sentence_name is shown to the user in a application, > I would also like to order it that way in the return results. > > SELECT DISTINCT k.kb_Classification_ekey AS [Classification key], > c.classification_name AS [Classification Name], vm.menu_name AS [Menu > Name], > k.sentence_name AS [Child Menu Name] > FROM > ingres_keyword AS k, ingres_kb_classification AS c, > ingres_view_keyword AS vk, ingres_view_menu AS vm > WHERE > k.kb_classification_ekey=c.ekey And > k.ekey=vk.keyword_ekey And vm.ekey=vk.view_menu_parent_ekey And > k.kb_classification_ekey In (129,229,279,294,312,313,314,315,320) > ORDER BY k.kb_classification_ekey, vm.menu_name, k.sentence_name; > > thanks > gv > > > > > cut&paste prob..here's the correction.
SELECT k.kb_Classification_ekey AS [Classification key], c.classification_name AS [Classification Name], vm.menu_name AS [Menu Name], k.sentence_name AS [Child Menu Name] FROM ingres_keyword AS k, ingres_kb_classification AS c, ingres_view_keyword AS vk, ingres_view_menu AS vm WHERE k.kb_classification_ekey=c.ekey And k.ekey=vk.keyword_ekey And vm.ekey=vk.view_menu_parent_ekey And k.kb_classification_ekey In (129,229,279,294,312,313,314,315,320) group by k.kb_Classification_ekey,c.classification_name,vm.menu_name,k.sentence_name ORDER BY k.kb_classification_ekey, vm.menu_name, k.sentence_name,max(vk.display_order); -- Show quote-oj "oj" <nospam_ojngo@home.com> wrote in message news:e4wVW5TxFHA.3152@TK2MSFTNGP10.phx.gbl... > SELECT k.kb_Classification_ekey AS [Classification key], > c.classification_name AS [Classification Name], > vm.menu_name AS [Menu Name], > k.sentence_name AS [Child Menu Name] > FROM > ingres_keyword AS k, ingres_kb_classification AS c, > ingres_view_keyword AS vk, ingres_view_menu AS vm > WHERE > k.kb_classification_ekey=c.ekey And > k.ekey=vk.keyword_ekey And vm.ekey=vk.view_menu_parent_ekey And > k.kb_classification_ekey In (129,229,279,294,312,313,314,315,320) > group by k.kb_Classification_ekey,c.classification_name,vm.menu_name AS > [Menu Name], > k.sentence_name > ORDER BY k.kb_classification_ekey, vm.menu_name, > k.sentence_name,max(vk.display_order); > > -- > -oj > > > "gv" <viat***@musc.edu> wrote in message > news:uHNlqBTxFHA.3236@TK2MSFTNGP14.phx.gbl... >> Hi all, >> >> I Apologize if I'm not clear, I'm going to try and explain what I need. >> >> This query works fine: On the table "k" there is a display_order column >> that I would like the k.sentence_name ordered by. >> If I add vk.display_order in the order by, I have to add it as well in >> the select but, this screws up using Distinct in the Select? >> >> The display_order column basically stores a number value indicating the >> order of the k.sentence_name is shown to the user in a application, >> I would also like to order it that way in the return results. >> >> SELECT DISTINCT k.kb_Classification_ekey AS [Classification key], >> c.classification_name AS [Classification Name], vm.menu_name AS [Menu >> Name], >> k.sentence_name AS [Child Menu Name] >> FROM >> ingres_keyword AS k, ingres_kb_classification AS c, >> ingres_view_keyword AS vk, ingres_view_menu AS vm >> WHERE >> k.kb_classification_ekey=c.ekey And >> k.ekey=vk.keyword_ekey And vm.ekey=vk.view_menu_parent_ekey And >> k.kb_classification_ekey In >> (129,229,279,294,312,313,314,315,320) >> ORDER BY k.kb_classification_ekey, vm.menu_name, k.sentence_name; >> >> thanks >> gv >> >> >> >> >> > > Thanks oj for your help,
I just relized that I posted this in the sqlserver programming group, I'm doing this in Access 2003 and gives me an error that I cannot use max in the order by clause. Any suggestions on what to do in Access? sorry thanks again gv Show quote "oj" <nospam_ojngo@home.com> wrote in message news:e4wVW5TxFHA.3152@TK2MSFTNGP10.phx.gbl... > SELECT k.kb_Classification_ekey AS [Classification key], > c.classification_name AS [Classification Name], > vm.menu_name AS [Menu Name], > k.sentence_name AS [Child Menu Name] > FROM > ingres_keyword AS k, ingres_kb_classification AS c, > ingres_view_keyword AS vk, ingres_view_menu AS vm > WHERE > k.kb_classification_ekey=c.ekey And > k.ekey=vk.keyword_ekey And vm.ekey=vk.view_menu_parent_ekey And > k.kb_classification_ekey In (129,229,279,294,312,313,314,315,320) > group by k.kb_Classification_ekey,c.classification_name,vm.menu_name AS > [Menu Name], > k.sentence_name > ORDER BY k.kb_classification_ekey, vm.menu_name, > k.sentence_name,max(vk.display_order); > > -- > -oj > > > "gv" <viat***@musc.edu> wrote in message > news:uHNlqBTxFHA.3236@TK2MSFTNGP14.phx.gbl... >> Hi all, >> >> I Apologize if I'm not clear, I'm going to try and explain what I need. >> >> This query works fine: On the table "k" there is a display_order column >> that I would like the k.sentence_name ordered by. >> If I add vk.display_order in the order by, I have to add it as well in >> the select but, this screws up using Distinct in the Select? >> >> The display_order column basically stores a number value indicating the >> order of the k.sentence_name is shown to the user in a application, >> I would also like to order it that way in the return results. >> >> SELECT DISTINCT k.kb_Classification_ekey AS [Classification key], >> c.classification_name AS [Classification Name], vm.menu_name AS [Menu >> Name], >> k.sentence_name AS [Child Menu Name] >> FROM >> ingres_keyword AS k, ingres_kb_classification AS c, >> ingres_view_keyword AS vk, ingres_view_menu AS vm >> WHERE >> k.kb_classification_ekey=c.ekey And >> k.ekey=vk.keyword_ekey And vm.ekey=vk.view_menu_parent_ekey And >> k.kb_classification_ekey In >> (129,229,279,294,312,313,314,315,320) >> ORDER BY k.kb_classification_ekey, vm.menu_name, k.sentence_name; >> >> thanks >> gv >> >> >> >> >> > > Try:
select [Classification key],[Classification Name], [Menu Name],[Child Menu Name] from ( SELECT k.kb_Classification_ekey AS [Classification key], c.classification_name AS [Classification Name], vm.menu_name AS [Menu Name], k.sentence_name AS [Child Menu Name], max(vk.display_order) as [ord] FROM ingres_keyword AS k, ingres_kb_classification AS c, ingres_view_keyword AS vk, ingres_view_menu AS vm WHERE k.kb_classification_ekey=c.ekey And k.ekey=vk.keyword_ekey And vm.ekey=vk.view_menu_parent_ekey And k.kb_classification_ekey In (129,229,279,294,312,313,314,315,320) group by k.kb_Classification_ekey,c.classification_name,vm.menu_name,k.sentence_name ) as derived ORDER BY [Classification key],[Classification Name],[Child Menu Name],[ord] -- Show quote-oj "gv" <viat***@musc.edu> wrote in message news:OHaOF$TxFHA.2076@TK2MSFTNGP14.phx.gbl... > Thanks oj for your help, > > I just relized that I posted this in the sqlserver programming group, I'm > doing this in Access 2003 and > gives me an error that I cannot use max in the order by clause. > Any suggestions on what to do in Access? sorry > > thanks again > gv > > > "oj" <nospam_ojngo@home.com> wrote in message > news:e4wVW5TxFHA.3152@TK2MSFTNGP10.phx.gbl... >> SELECT k.kb_Classification_ekey AS [Classification key], >> c.classification_name AS [Classification Name], >> vm.menu_name AS [Menu Name], >> k.sentence_name AS [Child Menu Name] >> FROM >> ingres_keyword AS k, ingres_kb_classification AS c, >> ingres_view_keyword AS vk, ingres_view_menu AS vm >> WHERE >> k.kb_classification_ekey=c.ekey And >> k.ekey=vk.keyword_ekey And vm.ekey=vk.view_menu_parent_ekey And >> k.kb_classification_ekey In >> (129,229,279,294,312,313,314,315,320) >> group by k.kb_Classification_ekey,c.classification_name,vm.menu_name AS >> [Menu Name], >> k.sentence_name >> ORDER BY k.kb_classification_ekey, vm.menu_name, >> k.sentence_name,max(vk.display_order); >> >> -- >> -oj >> >> >> "gv" <viat***@musc.edu> wrote in message >> news:uHNlqBTxFHA.3236@TK2MSFTNGP14.phx.gbl... >>> Hi all, >>> >>> I Apologize if I'm not clear, I'm going to try and explain what I need. >>> >>> This query works fine: On the table "k" there is a display_order column >>> that I would like the k.sentence_name ordered by. >>> If I add vk.display_order in the order by, I have to add it as well in >>> the select but, this screws up using Distinct in the Select? >>> >>> The display_order column basically stores a number value indicating the >>> order of the k.sentence_name is shown to the user in a application, >>> I would also like to order it that way in the return results. >>> >>> SELECT DISTINCT k.kb_Classification_ekey AS [Classification key], >>> c.classification_name AS [Classification Name], vm.menu_name AS [Menu >>> Name], >>> k.sentence_name AS [Child Menu Name] >>> FROM >>> ingres_keyword AS k, ingres_kb_classification AS c, >>> ingres_view_keyword AS vk, ingres_view_menu AS vm >>> WHERE >>> k.kb_classification_ekey=c.ekey And >>> k.ekey=vk.keyword_ekey And vm.ekey=vk.view_menu_parent_ekey And >>> k.kb_classification_ekey In >>> (129,229,279,294,312,313,314,315,320) >>> ORDER BY k.kb_classification_ekey, vm.menu_name, k.sentence_name; >>> >>> thanks >>> gv >>> >>> >>> >>> >>> >> >> > > Thanks again oj for your help
still same error. that error in Access 2003 is Use of aggregates inside the order by clause is illegal. (#328618) thanks gv Show quote "oj" <nospam_ojngo@home.com> wrote in message news:u%23Yz4DUxFHA.1148@TK2MSFTNGP11.phx.gbl... > Try: > > select [Classification key],[Classification Name], > [Menu Name],[Child Menu Name] > from ( > SELECT k.kb_Classification_ekey AS [Classification key], > c.classification_name AS [Classification Name], > vm.menu_name AS [Menu Name], > k.sentence_name AS [Child Menu Name], > max(vk.display_order) as [ord] > FROM > ingres_keyword AS k, ingres_kb_classification AS c, > ingres_view_keyword AS vk, ingres_view_menu AS vm > WHERE > k.kb_classification_ekey=c.ekey And > k.ekey=vk.keyword_ekey And vm.ekey=vk.view_menu_parent_ekey And > k.kb_classification_ekey In (129,229,279,294,312,313,314,315,320) > group by > k.kb_Classification_ekey,c.classification_name,vm.menu_name,k.sentence_name > ) as derived > ORDER BY [Classification key],[Classification Name],[Child Menu > Name],[ord] > > > -- > -oj > > > "gv" <viat***@musc.edu> wrote in message > news:OHaOF$TxFHA.2076@TK2MSFTNGP14.phx.gbl... >> Thanks oj for your help, >> >> I just relized that I posted this in the sqlserver programming group, I'm >> doing this in Access 2003 and >> gives me an error that I cannot use max in the order by clause. >> Any suggestions on what to do in Access? sorry >> >> thanks again >> gv >> >> >> "oj" <nospam_ojngo@home.com> wrote in message >> news:e4wVW5TxFHA.3152@TK2MSFTNGP10.phx.gbl... >>> SELECT k.kb_Classification_ekey AS [Classification key], >>> c.classification_name AS [Classification Name], >>> vm.menu_name AS [Menu Name], >>> k.sentence_name AS [Child Menu Name] >>> FROM >>> ingres_keyword AS k, ingres_kb_classification AS c, >>> ingres_view_keyword AS vk, ingres_view_menu AS vm >>> WHERE >>> k.kb_classification_ekey=c.ekey And >>> k.ekey=vk.keyword_ekey And vm.ekey=vk.view_menu_parent_ekey And >>> k.kb_classification_ekey In >>> (129,229,279,294,312,313,314,315,320) >>> group by k.kb_Classification_ekey,c.classification_name,vm.menu_name AS >>> [Menu Name], >>> k.sentence_name >>> ORDER BY k.kb_classification_ekey, vm.menu_name, >>> k.sentence_name,max(vk.display_order); >>> >>> -- >>> -oj >>> >>> >>> "gv" <viat***@musc.edu> wrote in message >>> news:uHNlqBTxFHA.3236@TK2MSFTNGP14.phx.gbl... >>>> Hi all, >>>> >>>> I Apologize if I'm not clear, I'm going to try and explain what I need. >>>> >>>> This query works fine: On the table "k" there is a display_order >>>> column that I would like the k.sentence_name ordered by. >>>> If I add vk.display_order in the order by, I have to add it as well in >>>> the select but, this screws up using Distinct in the Select? >>>> >>>> The display_order column basically stores a number value indicating the >>>> order of the k.sentence_name is shown to the user in a application, >>>> I would also like to order it that way in the return results. >>>> >>>> SELECT DISTINCT k.kb_Classification_ekey AS [Classification key], >>>> c.classification_name AS [Classification Name], vm.menu_name AS [Menu >>>> Name], >>>> k.sentence_name AS [Child Menu Name] >>>> FROM >>>> ingres_keyword AS k, ingres_kb_classification AS c, >>>> ingres_view_keyword AS vk, ingres_view_menu AS vm >>>> WHERE >>>> k.kb_classification_ekey=c.ekey And >>>> k.ekey=vk.keyword_ekey And vm.ekey=vk.view_menu_parent_ekey >>>> And >>>> k.kb_classification_ekey In >>>> (129,229,279,294,312,313,314,315,320) >>>> ORDER BY k.kb_classification_ekey, vm.menu_name, k.sentence_name; >>>> >>>> thanks >>>> gv >>>> >>>> >>>> >>>> >>>> >>> >>> >> >> > > Just did a quick test. It worked for me here.
SELECT CustomerID,OrderID FROM Orders GROUP BY CustomerID,OrderID ORDER BY CustomerID,OrderID,Max(EmployeeID); Can you post the exact query that generates the error. -- Show quote-oj "gv" <viat***@musc.edu> wrote in message news:%23Gl21RUxFHA.2880@TK2MSFTNGP12.phx.gbl... > Thanks again oj for your help > > still same error. that error in Access 2003 is > Use of aggregates inside the order by clause > is illegal. (#328618) > > thanks > gv > > > "oj" <nospam_ojngo@home.com> wrote in message > news:u%23Yz4DUxFHA.1148@TK2MSFTNGP11.phx.gbl... >> Try: >> >> select [Classification key],[Classification Name], >> [Menu Name],[Child Menu Name] >> from ( >> SELECT k.kb_Classification_ekey AS [Classification key], >> c.classification_name AS [Classification Name], >> vm.menu_name AS [Menu Name], >> k.sentence_name AS [Child Menu Name], >> max(vk.display_order) as [ord] >> FROM >> ingres_keyword AS k, ingres_kb_classification AS c, >> ingres_view_keyword AS vk, ingres_view_menu AS vm >> WHERE >> k.kb_classification_ekey=c.ekey And >> k.ekey=vk.keyword_ekey And vm.ekey=vk.view_menu_parent_ekey And >> k.kb_classification_ekey In >> (129,229,279,294,312,313,314,315,320) >> group by >> k.kb_Classification_ekey,c.classification_name,vm.menu_name,k.sentence_name >> ) as derived >> ORDER BY [Classification key],[Classification Name],[Child Menu >> Name],[ord] >> >> >> -- >> -oj >> >> >> "gv" <viat***@musc.edu> wrote in message >> news:OHaOF$TxFHA.2076@TK2MSFTNGP14.phx.gbl... >>> Thanks oj for your help, >>> >>> I just relized that I posted this in the sqlserver programming group, >>> I'm >>> doing this in Access 2003 and >>> gives me an error that I cannot use max in the order by clause. >>> Any suggestions on what to do in Access? sorry >>> >>> thanks again >>> gv >>> >>> >>> "oj" <nospam_ojngo@home.com> wrote in message >>> news:e4wVW5TxFHA.3152@TK2MSFTNGP10.phx.gbl... >>>> SELECT k.kb_Classification_ekey AS [Classification key], >>>> c.classification_name AS [Classification Name], >>>> vm.menu_name AS [Menu Name], >>>> k.sentence_name AS [Child Menu Name] >>>> FROM >>>> ingres_keyword AS k, ingres_kb_classification AS c, >>>> ingres_view_keyword AS vk, ingres_view_menu AS vm >>>> WHERE >>>> k.kb_classification_ekey=c.ekey And >>>> k.ekey=vk.keyword_ekey And vm.ekey=vk.view_menu_parent_ekey >>>> And >>>> k.kb_classification_ekey In >>>> (129,229,279,294,312,313,314,315,320) >>>> group by k.kb_Classification_ekey,c.classification_name,vm.menu_name AS >>>> [Menu Name], >>>> k.sentence_name >>>> ORDER BY k.kb_classification_ekey, vm.menu_name, >>>> k.sentence_name,max(vk.display_order); >>>> >>>> -- >>>> -oj >>>> >>>> >>>> "gv" <viat***@musc.edu> wrote in message >>>> news:uHNlqBTxFHA.3236@TK2MSFTNGP14.phx.gbl... >>>>> Hi all, >>>>> >>>>> I Apologize if I'm not clear, I'm going to try and explain what I >>>>> need. >>>>> >>>>> This query works fine: On the table "k" there is a display_order >>>>> column that I would like the k.sentence_name ordered by. >>>>> If I add vk.display_order in the order by, I have to add it as well in >>>>> the select but, this screws up using Distinct in the Select? >>>>> >>>>> The display_order column basically stores a number value indicating >>>>> the >>>>> order of the k.sentence_name is shown to the user in a application, >>>>> I would also like to order it that way in the return results. >>>>> >>>>> SELECT DISTINCT k.kb_Classification_ekey AS [Classification key], >>>>> c.classification_name AS [Classification Name], vm.menu_name AS [Menu >>>>> Name], >>>>> k.sentence_name AS [Child Menu Name] >>>>> FROM >>>>> ingres_keyword AS k, ingres_kb_classification AS c, >>>>> ingres_view_keyword AS vk, ingres_view_menu AS vm >>>>> WHERE >>>>> k.kb_classification_ekey=c.ekey And >>>>> k.ekey=vk.keyword_ekey And vm.ekey=vk.view_menu_parent_ekey >>>>> And >>>>> k.kb_classification_ekey In >>>>> (129,229,279,294,312,313,314,315,320) >>>>> ORDER BY k.kb_classification_ekey, vm.menu_name, k.sentence_name; >>>>> >>>>> thanks >>>>> gv >>>>> >>>>> >>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics