order by

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
gvSELECT 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" <viatorg@.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);
-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" <viatorg@.musc.edu> wrote in message
> news:uHNlqBTxFHA.3236@.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" <viatorg@.musc.edu> wrote in message
> news:uHNlqBTxFHA.3236@.TK2MSFTNGP14.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" <viatorg@.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...
>|||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_nam
e
> ) as derived
> ORDER BY [Classification key],[Classification Name],[Child Menu
> Name],[ord]
>
> --
> -oj
>
> "gv" <viatorg@.musc.edu> wrote in message
> news:OHaOF$TxFHA.2076@.TK2MSFTNGP14.phx.gbl...
>|||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.
-oj
"gv" <viatorg@.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...
>sql