SQL statement : How to Creat Last in this Sql

[nader]

I'm using DB MS Access. with Vb2005.
I have this select:
" SELECT LAST(BNotice),LAST(BAgent),LAST(BPriceS), SUM(IIF(BType='Buy',BAmount,-BAmount,)),BSource,BClass,BKind,BMaterial FROM TBbills Where BType IN ('Damage','Sell','Buy') and Bmdeleted ='Yes' GROUP BY BSource,BClass,BKind,BMaterial"

I Have another Filde in the Table "TBbills Called History and it's Typed Date/Time.
I want to define the LAST(BAgent),LAST(BPriceS) according with the Last HIstory.

[dee-u]

You could use ORDER BY.

[nader]

I tried like this but didn't succcess
" SELECT BNotice,BAgent,BPriceS, SUM(IIF(BType='Buy',BAmount,-BAmount,)),BSource,BClass,BKind,BMaterial FROM TBbills Where BType IN ('Damage','Sell','Buy') and Bmdeleted ='Yes' GROUP BY BSource,BClass,BKind,BMaterial order by Last(BHistory)"

although the previous Sql is working well.
but this sql it showed me this error:
You tried to execute a query that does not include the specified expression 'BNotice' as part of an aggregate function.

[GaryMazzone]

The message say it all.... you are missing BNotice and BAgent fin the Group statement.

I would be very careful about using Last also. That may work in Access but probably will not in another database system.

[nader]

So How can I select the BNotice,BAgent,BPriceS order by Max (History). in that Sql

[dee-u]

Something like this?

SELECT BNotice,BAgent,BPriceS FROM TBbills ORDER BY History DESC

To retrieve only the topmost record then add the TOP clause.

SELECT TOP 1 BNotice,BAgent,BPriceS FROM TBbills ORDER BY History DESC

[nader]

Oky, I got this, but how to use it in that Sql:
it's mean like this:
SELECT BNotice,BAgent,BPriceS, SUM(IIF(BType='Buy',BAmount,-BAmount,)),BSource,BClass,BKind,BMaterial FROM TBbills Where BType IN ('Damage','Sell','Buy') and Bmdeleted ='Yes' GROUP BY BSource,BClass,BKind,BMaterial ORDER BY History DESC"

this sql it didn't work well

[dee-u]

What did you mean by 'it didn't work well'?

[nader]

I'm sorry I didn't inser the right Sql
this is the sql I tried and it made this error:
" SELECT BSource,BClass,BKind,BMaterial, SUM(IIF(BType='Buy',BAmount,-BAmount,)),(BNotice,BAgent,BPriceS order by BHistory DESC ) FROM TBbills Where BType IN ('Damage','Sell','Buy') and Bmdeleted ='Yes' GROUP BY BSource,BClass,BKind,BMaterial "

the error:
Syntax error (missing operator) in query expression '(BNotice,BAgent,BPriceS order by BHistory DESC )'.

[dee-u]

As odd as it is I cannot find '(BNotice,BAgent,BPriceS order by BHistory DESC )' in your sql so I cannot suppose that it has got something to do with the error, you should post the sql which is causing the error.

[GaryMazzone]

This:

,(BNotice,BAgent,BPriceS order by BHistory DESC )

Is not valid in an SQL Statement At leaset not where you have placed it

[dee-u]

Oooppsss... So they are in the middle that's why I cannot find them. And they are not supposed to be there.

[nader]

I tried this Sql and it succeed with me.

" SELECT Last(BNotice),Last(BAgent),Last(BPriceS), SUM(IIF(BType='Buy',BAmount,-BAmount,)),BSource,BClass,BKind,BMaterial FROM TBbills Where BType IN  ('Damage','Sell','Buy')  and Bmdeleted ='Yes' GROUP BY BSource,BClass,BKind,BMaterial "

I used this Last(BNotice),Last(BAgent),Last(BPriceS) in that sql
I wan to select these three columns in that sql order by BHistory DESC. Not by Last.. that all I want.

[dee-u]

If you don't want to use LAST then why are you still using it?

[leinad31]

Its best to post relevant table definitions and sample correct output, rather than hoping people can guess what you want based on incorrect SQL. Help us help you.

[nader]

I used the "Last" with three columns (BNotice,BAgent,BPriceS). Now I want to make modification by define these columns by "order by BHistory DESC".

For more clear, I'll offe the issue more easy and make it short:
If I have these Columns(A,B,C,D,E.F,G,H) in the Table1. The typed of A,B,C,E,F,G is text and D is number and H is Date/time..
and here is the data in that table

A------B------C------D------F------G------H
dd2---cc5-----ff8----11-----tt5----uu4---4 dec 08

kk8---cc7----ss6----33-----mm2---zz5----2 dec 08

dd2--cc5----ff8-----44----gg3-----yy5-----1 dec 08

If I use this sql:

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

the result will be like this
A------B------C------D------F------G
dd2--cc5----ff8-----55----gg3-----yy5 ' according by Gruop and Last
kk8---cc7----ss6----33-----mm2---zz5

but I want to make modification on that sql by change the condition of select the Last( F),Last( G), by " order by H DESC ".
to be the result like this:

A------B------C------D------F------G
dd2---cc5-----ff8----55-----tt5----uu4 ' according by Gruop and order by H DESC
kk8---cc7----ss6----33-----mm2---zz5


That's all I want. I hope I'm clear now and I'm sorry for confusing them. Thank you a lot.

[leinad31]

There's no primary/unique key per record?

Since use of Last() implies necessity that the records are sorted before aggregation then simply apply relevant processing/transformation per dataset (use intermediate "tables").

SELECT A, B, C, SUM(D)
FROM (SELECT A, B, C, D FROM yourTable ORDER BY H)
GROUP BY A, B, C

Note that aggregation and sorting are database CPU intensive (performance issue). What other columns and indexes are available so other query variants can be tested instead of being SQL output oriented.

[nader]

I tried this way:

"Select A,B,C SUM(D) FROM(Select F,G From Table1 ORDER BY H DESC) GROUP BY A, B, C "

It showed me this error:
No value given for one or more required parameters.

[dee-u]

Did you run it as quoted? You need to change the table name and the fields so it would match with what you have in your database.

[nader]

I created a simple one.
The table is a Table1 and the Columns are A,B,C,D,F,G,H

[dee-u]

To perform an ORDER you should try it like this

SELECT * FROM Table1 ORDER BY H

[nader]

Do you mean I have to try like this

"Select A,B,C, SUM(D) FROM(Select * F,G From Table1 ORDER BY H DESC) GROUP BY A, B, C "

this will show syntax error on F

[dee-u]

Do you mean I have to try like this

this will show syntax error on F

Before trying out a more complex sql you should try it out like the one I posted just to get you the feeling on how it is done.

[nader]

Before trying out a more complex sql you should try it out like the one I posted just to get you the feeling on how it is done.

I tried before and it succeed with me.

The problem in using it in this sql

"Select A,B,C, SUM(D) FROM(Select * F,G From Table1 ORDER BY H DESC) GROUP BY A, B, C "

[leinad31]

You obviously can't select/aggregate on columns A, B, C, D if the source view/table doesn't contain those columns; only columns F and G were returned to succeeding/outer SELECT statement.

[leinad31]

Because

SELECT * F, G

isn't valid SQL syntax. You making this seem harder than it really is. It's a simple two step process, return recordset (imagine your returning a table view) ordered by H with all columns needed for next step... you can even check first that SQL is valid before using it as the source in next step... Next step is to then perform aggregation on first query's result (or treat it as a table view), you do group by, sum(), last() on relevant columns.

Emphasis on relevant columns, declare columns correctly and completely. Get your columns right!! Its been several days and the column list is still wrong.