last value of database

i want to get the last value of the database and the database look like this

table name prospec
TranID data item
1 02/02/2004 2
2 02/02/2004 2
3 02/02/2004 3
4 02/02/2004 3
i want to get the value (4) which auto generate by itself
i try "select TranID from prospec order by TranID desc limit 1"
then when i run it then this error came out "incorrect syntax new 'limit' "Originally posted by mrbear
i want to get the last value of the database and the database look like this

table name prospec
TranID data item
1 02/02/2004 2
2 02/02/2004 2
3 02/02/2004 3
4 02/02/2004 3
i want to get the value (4) which auto generate by itself
i try "select TranID from prospec order by TranID desc limit 1"
then when i run it then this error came out "incorrect syntax new 'limit' "

What type of database are you using?|||Originally posted by ika
What type of database are you using?
i'm using sqlserver databse|||Originally posted by mrbear
i want to get the last value of the database and the database look like this

table name prospec
TranID data item
1 02/02/2004 2
2 02/02/2004 2
3 02/02/2004 3
4 02/02/2004 3
i want to get the value (4) which auto generate by itself
i try "select TranID from prospec order by TranID desc limit 1"
then when i run it then this error came out "incorrect syntax new 'limit' " For MS-SQL 7.0 or later, you probably want:SELECT TOP 1 TranID
FROM prospec
ORDER BY TranID DESCYou can actually get the same result much more portably by using:SELECT Max(TranID)
FROM prospecThis should work on any database that supports SQL-89 or later.

-PatP|||Originally posted by Pat Phelan
For MS-SQL 7.0 or later, you probably want:SELECT TOP 1 TranID
FROM prospec
ORDER BY TranID DESCYou can actually get the same result much more portably by using:SELECT Max(TranID)
FROM prospecThis should work on any database that supports SQL-89 or later.

-PatP

In SQL 7 or later, you also want:

SELECT
MAX(TranID)
FROM
prospec|||thanks guys, the statment works. Thanks a million times|||Originally posted by derrickleggett
In SQL 7 or later, you also want:

SELECT
MAX(TranID)
FROM
prospec also?

isn't that what Pat said? you even quoted him!!!!!!

and what's up with the "SQL 7 or later" qualification??

are you saying select max(foo) from bar won't work in 6.5??

whoasql

Last Three Full Months?

Hi,
I have sales line item detail like
invoicedate, custid, ordernum
Is there a way using SQL to only return records for the last three full
months? For example, if today is 2/4/05, I would want records with
invoicedate between 11/1/04 and 1/31/05.
TIA,
DanBreak down the problem. First, you want the first day of the current month:
convert (char 6), getdate, 112) + '01'
Next, you want from 3 months previous, up to but not including the first of
this month. The 3 months previous are:
dateadd (mo, -3, convert (char 6), getdate, 112) + '01')
Now, put these into your WHERE clause:
WHERE
MyDate > = dateadd (mo, -3, convert (char 6), getdate, 112) + '01')
and MyDate < convert (char 6), getdate, 112) + '01'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Dan" <dan@.nospam.com> wrote in message
news:IFmne.496$Wk4.8376@.news.uswest.net...
Hi,
I have sales line item detail like
invoicedate, custid, ordernum
Is there a way using SQL to only return records for the last three full
months? For example, if today is 2/4/05, I would want records with
invoicedate between 11/1/04 and 1/31/05.
TIA,
Dan|||declare @.date smalldatetime
declare @.start smalldatetime
declare @.end smalldatetime
set @.date = '2004-02-04'
set @.start = dateadd(d, -(datepart(d, @.date)) + 1, dateadd(m, -3, @.date))
set @.end = dateadd(d, -(datepart(d, @.date)) + 1, @.date)
select ... from invoice where invoiceDate >= @.start and invoiceDate < @.end
"Dan" <dan@.nospam.com> wrote in message
news:IFmne.496$Wk4.8376@.news.uswest.net...
> Hi,
> I have sales line item detail like
> invoicedate, custid, ordernum
> Is there a way using SQL to only return records for the last three full
> months? For example, if today is 2/4/05, I would want records with
> invoicedate between 11/1/04 and 1/31/05.
> TIA,
> Dan|||On 6/1/2005 1:10 PM, Tom Moreau wrote:
> Break down the problem. First, you want the first day of the current mont
h:
> convert (char 6), getdate, 112) + '01'
> Next, you want from 3 months previous, up to but not including the first o
f
> this month. The 3 months previous are:
> dateadd (mo, -3, convert (char 6), getdate, 112) + '01')
> Now, put these into your WHERE clause:
> WHERE
> MyDate > = dateadd (mo, -3, convert (char 6), getdate, 112) + '01')
> and MyDate < convert (char 6), getdate, 112) + '01'
In my query tool (Brio) I am getting invalid syntax error. Can you
double check syntax as I am not familiar with these commands? BTW what
does 112 signify?
Thanks,
Dan|||On 6/1/2005 1:22 PM, Dan wrote:
> On 6/1/2005 1:10 PM, Tom Moreau wrote:
>
>
> In my query tool (Brio) I am getting invalid syntax error. Can you
> double check syntax as I am not familiar with these commands? BTW what
> does 112 signify?
> Thanks,
> Dan
>
I fixed the code to run now. What does + '01' do?
Thanks,
Dan|||Sorry about that. I'm a little distracted by back pain right now. Try
this:
WHERE
MyDate > = dateadd (mm, -3, convert (char (6), getdate(), 112) + '01')
and MyDate < convert (char (6), getdate(), 112) + '01'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Dan" <dan@.nospam.com> wrote in message
news:E1nne.498$Wk4.9251@.news.uswest.net...
On 6/1/2005 1:10 PM, Tom Moreau wrote:
> Break down the problem. First, you want the first day of the current
> month:
> convert (char 6), getdate, 112) + '01'
> Next, you want from 3 months previous, up to but not including the first
> of
> this month. The 3 months previous are:
> dateadd (mo, -3, convert (char 6), getdate, 112) + '01')
> Now, put these into your WHERE clause:
> WHERE
> MyDate > = dateadd (mo, -3, convert (char 6), getdate, 112) + '01')
> and MyDate < convert (char 6), getdate, 112) + '01'
In my query tool (Brio) I am getting invalid syntax error. Can you
double check syntax as I am not familiar with these commands? BTW what
does 112 signify?
Thanks,
Dan|||The conversion to char (6) gives you the year and month only in YYYMM
format. You still need a day, and you supply that by concatenating '01'.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Dan" <dan@.nospam.com> wrote in message
news:1lnne.499$Wk4.9860@.news.uswest.net...
On 6/1/2005 1:22 PM, Dan wrote:
> On 6/1/2005 1:10 PM, Tom Moreau wrote:
>
>
> In my query tool (Brio) I am getting invalid syntax error. Can you
> double check syntax as I am not familiar with these commands? BTW what
> does 112 signify?
> Thanks,
> Dan
>
I fixed the code to run now. What does + '01' do?
Thanks,
Dan|||On 6/1/2005 1:51 PM, Tom Moreau wrote:
> The conversion to char (6) gives you the year and month only in YYYMM
> format. You still need a day, and you supply that by concatenating '01'.
>
Thanks a bunch!
Dan|||Another alternative:
SELECT DATEADD( m, -3, @.dt - DAY( @.dt ) + 1 ) AS "start",
@.dt - DAY( @.dt ) AS "end"
-- where @.dt is the date value in question
Anith

Last report item or RowNumber for details grouping

I have a report with details grouping on table. What i need to do is put row number only on Parent row and skip the child row. When i use RowNumber("GroupName") of course it gives me a current RowNumber. Is there a way to count only parents?

This post may help.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=903428&SiteID=1

cheers,

Andrew

|||

Hello,

Try putting this in the same row as your parent group row:

=RunningValue(Fields!GroupFieldName.Value, CountDistinct, nothing)

Jarret