Sql problem

Mangore · December 26th, 2008, 07:21 AM

I have Table1 in Db Ms Access
the Table has these columns A,B,C,D,E,F,G,H.. the typed of H is Date/time and the Typed of D is number and other columns is Text
I tried this Sql

Code:

 
Select A,B,C, SUM(D), (F,G  ORDER BY H DESC) FROM Table1 Group by A,B,C

The problem is in this line of Sql (F,G ORDER BY H DESC)
How do I fix this problem ?

Imar · December 26th, 2008, 07:31 AM

Quote:

The problem is in this line of Sql (F,G ORDER BY H DESC)

What problem? Do you get an error? Hard to suggest a fix if we don't know what the problem is....

Maybe the problem is caused by the parentheses around this statement?

Imar

happygv · December 26th, 2008, 08:35 AM

In addition to Imar's suggestions, I think the statement is syntactically wrong.

In your case SELECT... FROM... GROUP BY... ORDER BY is the sequence.

Mangore · December 26th, 2008, 08:58 AM

Here is the error:
Syntax error (missing operator) in query expression '(F,C,order by H DESC)'.

Old Pedant · December 26th, 2008, 04:28 PM

It is very hard to GUESS what you are trying to do.

You can NOT put an ORDER BY any place except at the VERY END of a SELECT query.

Thus:

Select A,B,C, SUM(D) FROM Table1 Group by A,B,C ORDER BY H DESC

But I suspect that is not what you want, since you don't SELECT H even though you ORDER BY it.

I *suspect* you want

Select A, B, C, SUM(D), F, G, H
FROM Table1
Group by A, B, C, F, G, H
ORDER BY H DESC

But who knows?? You don't give us enough information to help you.

Mangore · December 26th, 2008, 08:50 PM

I used this select before, and it succeed with me

Quote:

Select Last( F),Last( G), SUM(D), A,B,C FROM Table1 Group by A, B, C

Now I want to make modify on that Select by Select the F,G according the max value of H.
it's mean if the H = 1/12/2008 and 3/12/2008 and 2/12/2008 will select the F,G according the 3/12/2008

Old Pedant · December 27th, 2008, 01:33 AM

Quote:

Originally Posted by Mangore

Now I want to make modify on that Select by Select the F,G according the max value of H.
it's mean if the H = 1/12/2008 and 3/12/2008 and 2/12/2008 will select the F,G according the 3/12/2008

And how do you guarantee that A, B, and C will be correct, then??? Or even SUM(D), for that matter??

Example:

Code:

A    ::   B  ::   C ::   D ::  F  ::  G  ::  H
xxx  :: yyy  :: zzz :: 111 :: fff :: ggg :: 1 Dec 08
xxx  :: yyy  :: zzz :: 222 :: rrr :: sss :: 2 Dec 08
x99  :: y99  :: z99 :: 432 :: uuu :: vvv :: 3 Dec 08

If you JUST look at

Code:

SELECT A,B,C,SUM(D) FROM table1 GROUP BY A,B,C

you would get

Code:

A    ::   B  ::   C :: SUM(D)
xxx  :: yyy  :: zzz :: 333 
x99  :: y99  :: z99 :: 432

Now, if we go with MAX(H), which is 3 Dec 08, what results do you WANT???

I think you need to show us some SAMPLE DATA. And the RESULTS you want from that data.

Mangore · December 27th, 2008, 07:47 AM

As I told you I used this Sql:

Quote:

Select Last( F),Last( G), SUM(D), A,B,C FROM Table1 Group by A, B, C

and the result was like this:

Code:

 
 
A   :: B    :: C    :: D  :: F :: G 
xxx :: yyy :: zzz :: 333 :: rrr :: sss

I want to make modification on that sql by define that row not by Last, but by MAX(H), or Order By H DESC
it's mean if the Table like this:

Code:

 
A   :: B   :: C   :: D   :: F   :: G   :: H
xxx :: yyy :: zzz :: 111 :: fff :: ggg ::2 Dec 08
xxx :: yyy :: zzz :: 222 :: rrr :: sss :: 1 Dec 08
x99 :: y99 :: z99 :: 432 :: uuu :: vvv :: 3 Dec 08

the result will be like this

Code:

 
A  :: B    :: C   :: D   :: F   :: G 
xxx :: yyy :: zzz :: 333 :: fff :: ggg 
x99 :: y99 :: z99 :: 432 :: uuu :: vvv

Old Pedant · December 28th, 2008, 02:04 AM

Okay, you will note that this is what I *thought* you were after, but I could not be at all sure.

The point I was trying to clarify is whether the MAX(H) applied to the whole table or to what level of groups.

Let me play with it some. It's not trivial.

In SQL Server it can probably be done in a single query. But with Access we might have to create a stored query, first, and then another query against it.

Mangore · December 28th, 2008, 06:05 AM

Oky I'm waiting, and thank you for your effort.