-
SQL statement : How to Creat Last in this Sql
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.
-
Re: SQL statement : How to Creat Last in this Sql
-
Re: SQL statement : How to Creat Last in this Sql
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.
-
Re: SQL statement : How to Creat Last in this Sql
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.
-
Re: SQL statement : How to Creat Last in this Sql
So How can I select the BNotice,BAgent,BPriceS order by Max (History). in that Sql
-
Re: SQL statement : How to Creat Last in this Sql
Something like this?
Code:
SELECT BNotice,BAgent,BPriceS FROM TBbills ORDER BY History DESC
To retrieve only the topmost record then add the TOP clause.
Code:
SELECT TOP 1 BNotice,BAgent,BPriceS FROM TBbills ORDER BY History DESC
-
Re: SQL statement : How to Creat Last in this Sql
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
-
Re: SQL statement : How to Creat Last in this Sql
What did you mean by 'it didn't work well'?
-
Re: SQL statement : How to Creat Last in this Sql
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 )'.
-
Re: SQL statement : How to Creat Last in this Sql
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.
-
Re: SQL statement : How to Creat Last in this Sql
This:
,(BNotice,BAgent,BPriceS order by BHistory DESC )
Is not valid in an SQL Statement At leaset not where you have placed it
-
Re: SQL statement : How to Creat Last in this Sql
Oooppsss... So they are in the middle that's why I cannot find them. :blush: And they are not supposed to be there.:mad:
-
Re: SQL statement : How to Creat Last in this Sql
I tried this Sql and it succeed with me.
vb Code:
" 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.
-
Re: SQL statement : How to Creat Last in this Sql
If you don't want to use LAST then why are you still using it?
-
Re: SQL statement : How to Creat Last in this Sql
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.
-
Re: SQL statement : How to Creat Last in this Sql
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:
Quote:
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.
-
Re: SQL statement : How to Creat Last in this Sql
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.
-
Re: SQL statement : How to Creat Last in this Sql
I tried this way:
vb Code:
"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.
-
Re: SQL statement : How to Creat Last in this Sql
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.
-
Re: SQL statement : How to Creat Last in this Sql
I created a simple one.
The table is a Table1 and the Columns are A,B,C,D,F,G,H
-
Re: SQL statement : How to Creat Last in this Sql
To perform an ORDER you should try it like this
Code:
SELECT * FROM Table1 ORDER BY H
-
Re: SQL statement : How to Creat Last in this Sql
Do you mean I have to try like this
Quote:
"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
-
Re: SQL statement : How to Creat Last in this Sql
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.
-
Re: SQL statement : How to Creat Last in this Sql
Quote:
Originally Posted by nader
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.
-
Re: SQL statement : How to Creat Last in this Sql
Quote:
Originally Posted by dee-u
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
Code:
"Select A,B,C, SUM(D) FROM(Select * F,G From Table1 ORDER BY H DESC) GROUP BY A, B, C "
-
Re: SQL statement : How to Creat Last in this Sql
Because 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.