last value of database

mrbear · #1 (**permalink**) 04-14-04, 04:24

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' "

ika · #2 (**permalink**) 04-14-04, 06:20

Quote:

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?

mrbear · #3 (**permalink**) 04-14-04, 20:55

Quote:

Originally posted by ika
What type of database are you using?

i'm using sqlserver databse

Pat Phelan · #4 (**permalink**) 04-14-04, 22:16

Quote:

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:

PHP Code:


			
SELECT TOP 1 TranID

   FROM prospec

   ORDER BY TranID DESC

You can actually get the same result much more portably by using:

PHP Code:


			
SELECT Max(TranID)

   FROM prospec

This should work on any database that supports SQL-89 or later.

-PatP

derrickleggett · #5 (**permalink**) 04-14-04, 22:35

Quote:

Originally posted by Pat Phelan
For MS-SQL 7.0 or later, you probably want:

PHP Code:


			
SELECT TOP 1 TranID

   FROM prospec

   ORDER BY TranID DESC

You can actually get the same result much more portably by using:

PHP Code:


			
SELECT Max(TranID)

   FROM prospec

This 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

mrbear · #6 (**permalink**) 04-15-04, 06:33

thanks guys, the statment works. Thanks a million times

r937 · #7 (**permalink**) 04-15-04, 08:13

Quote:

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????

whoa

Internet Services

Web Development

Digital Marketing

Consumer Tech