Determining Primary Key

#1

Is there anyway, using a SELECT statement, I can determine what the
PRIMARY KEY is?

I want the select a field from the last row in a database. I'm using a
select statement
SELECT myfield FROM table WHERE primarykey - (SELECT MAX(primarykey)
FROM table)

Or is there a better way to get a field from the last row?

Thanks..

Bruce

#2

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA256

Quote:

Is there anyway, using a SELECT statement, I can determine what the
PRIMARY KEY is?

I want the select a field from the last row in a database. I'm using a
select statement
SELECT myfield FROM table WHERE primarykey - (SELECT MAX(primarykey)
FROM table)

Or is there a better way to get a field from the last row?

Thanks..

Bruce

PKEY - Different way/database, mysql: http://tinyurl.com/3ycmnrn

last: SELECT field FROM table ORDER BY pkey DESC LIMIT 1;

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iF4EAREIAAYFAk0gtWAACgkQfD3PECtxdkUrFQD8D9X6+cFzRT X0j55On2yc+bl/
GoUzZ7AbujZL5xSbBMsBAICIGnmnX/N+5TwXQQcFB4T/3jK4PgBuya1tArIdC0iz
=blPk
-----END PGP SIGNATURE-----

#3

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA256

Quote:

Is there anyway, using a SELECT statement, I can determine what the
PRIMARY KEY is?
Plus it can be defined over several fields.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iF4EAREIAAYFAk0gtYYACgkQfD3PECtxdkUXlwEAqLWi+q1LcF oFZtq6oA6rr71j
d/h/ZGOBeFi3oRVoNtIA/iU4PnlzTme+cpQFxbLyLzqMf9WLt81JZPvHkcxZv/kg
=tzi3
-----END PGP SIGNATURE-----

#4

On 1/2/2011 10:34 AM, bruce wrote:

Quote:

Is there anyway, using a SELECT statement, I can determine what the
PRIMARY KEY is?

I want the select a field from the last row in a database. I'm using a
select statement
SELECT myfield FROM table WHERE primarykey - (SELECT MAX(primarykey)
FROM table)

Or is there a better way to get a field from the last row?

Thanks..

Bruce

Define "last row"? Tables are by definition unordered. The only way
you can get a "last" (or "first") row is to ORDER BY a field.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

#5

On 02-01-11 19:50, Jerry Stuckle wrote:

Quote:

On 1/2/2011 10:34 AM, bruce wrote:
Is there anyway, using a SELECT statement, I can determine what the
PRIMARY KEY is?

I want the select a field from the last row in a database. I'm using a
select statement
SELECT myfield FROM table WHERE primarykey - (SELECT MAX(primarykey)
FROM table)

Or is there a better way to get a field from the last row?

Thanks..

Bruce

Define "last row"? Tables are by definition unordered. The only way you
can get a "last" (or "first") row is to ORDER BY a field.

But, if you read the original message:
"WHERE primarykey - (SELECT MAX(primarykey) FROM table)"

This will ALWAYS produce just 1 record, so what order are you talking about?

--
Luuk

#6

On 1/2/2011 3:17 PM, Luuk wrote:

Quote:

On 02-01-11 19:50, Jerry Stuckle wrote:
On 1/2/2011 10:34 AM, bruce wrote:
Is there anyway, using a SELECT statement, I can determine what the
PRIMARY KEY is?

I want the select a field from the last row in a database. I'm using a
select statement
SELECT myfield FROM table WHERE primarykey - (SELECT MAX(primarykey)
FROM table)

Or is there a better way to get a field from the last row?

Thanks..

Bruce

Define "last row"? Tables are by definition unordered. The only way you
can get a "last" (or "first") row is to ORDER BY a field.

But, if you read the original message:
"WHERE primarykey - (SELECT MAX(primarykey) FROM table)"

This will ALWAYS produce just 1 record, so what order are you talking
about?

That will get the row with the highest primary key, true. But that is
not necessarily the last row.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

#7

On Jan 2, 3:36*pm, Jerry Stuckle <jstuck... (AT) attglobal (DOT) net> wrote:

Quote:

On 1/2/2011 3:17 PM, Luuk wrote:

On 02-01-11 19:50, Jerry Stuckle wrote:
On 1/2/2011 10:34 AM, bruce wrote:
Is there anyway, using a SELECT statement, I can determine what the
PRIMARY KEY is?

I want the select a field from the last row in a database. I'm using a
select statement
SELECT myfield FROM table WHERE primarykey - (SELECT MAX(primarykey)
FROM table)

Or is there a better way to get a field from the last row?

Thanks..

Bruce

Define "last row"? Tables are by definition unordered. The only way you
can get a "last" (or "first") row is to ORDER BY a field.

But, if you read the original message:
"WHERE primarykey - (SELECT MAX(primarykey) FROM table)"

This will ALWAYS produce just 1 record, so what order are you talking
about?

That will get the row with the highest primary key, true. *But that is
not necessarily the last row.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstuck... (AT) attglobal (DOT) net
==================

Sorry. I should have said I wanted the "Highest" primary key...

So, is there some way I can find out the row with the "Highest"
primary key without knowing the name of the primary key.

This will give me the primary key name (SHOW INDEX FROM table) but I
don't know how to incorporate this information to my statement

SELECT myfield FROM table WHERE primarykey - (SELECT MAX(primarykey)
FROM table)

I also found the following which returns the primary key name, but
again I don't know how to incorporate the result into my SELECT.

SELECT k.COLUMN_NAME
FROM information_schema.table_constraints t
LEFT JOIN information_schema.key_column_usage k
USING(constraint_name,table_schema,table_name)
WHERE t.constraint_type='PRIMARY KEY'
AND t.table_schema=DATABASE()
AND t.table_name='mytable';

#8

On 02-01-11 22:31, bruce wrote:

Quote:

On Jan 2, 3:36 pm, Jerry Stuckle<jstuck... (AT) attglobal (DOT) net> wrote:
On 1/2/2011 3:17 PM, Luuk wrote:

On 02-01-11 19:50, Jerry Stuckle wrote:
On 1/2/2011 10:34 AM, bruce wrote:
Is there anyway, using a SELECT statement, I can determine what the
PRIMARY KEY is?

I want the select a field from the last row in a database. I'm using a
select statement
SELECT myfield FROM table WHERE primarykey - (SELECT MAX(primarykey)
FROM table)

Or is there a better way to get a field from the last row?

Thanks..

Bruce

Define "last row"? Tables are by definition unordered. The only way you
can get a "last" (or "first") row is to ORDER BY a field.

But, if you read the original message:
"WHERE primarykey - (SELECT MAX(primarykey) FROM table)"

This will ALWAYS produce just 1 record, so what order are you talking
about?

That will get the row with the highest primary key, true. But that is
not necessarily the last row.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstuck... (AT) attglobal (DOT) net
==================

Sorry. I should have said I wanted the "Highest" primary key...

So, is there some way I can find out the row with the "Highest"
primary key without knowing the name of the primary key.

This will give me the primary key name (SHOW INDEX FROM table) but I
don't know how to incorporate this information to my statement

SELECT myfield FROM table WHERE primarykey - (SELECT MAX(primarykey)
FROM table)

I also found the following which returns the primary key name, but
again I don't know how to incorporate the result into my SELECT.

SELECT k.COLUMN_NAME
FROM information_schema.table_constraints t
LEFT JOIN information_schema.key_column_usage k
USING(constraint_name,table_schema,table_name)
WHERE t.constraint_type='PRIMARY KEY'
AND t.table_schema=DATABASE()
AND t.table_name='mytable';

The easiest way to do it is probably dynamically build a new SQL
statement with the info returned from your statement ( using PHP or
another language.)

--
Luuk

#9

On 1/2/2011 4:31 PM, bruce wrote:

Quote:

On Jan 2, 3:36 pm, Jerry Stuckle<jstuck... (AT) attglobal (DOT) net> wrote:
On 1/2/2011 3:17 PM, Luuk wrote:

On 02-01-11 19:50, Jerry Stuckle wrote:
On 1/2/2011 10:34 AM, bruce wrote:
Is there anyway, using a SELECT statement, I can determine what the
PRIMARY KEY is?

I want the select a field from the last row in a database. I'm using a
select statement
SELECT myfield FROM table WHERE primarykey - (SELECT MAX(primarykey)
FROM table)

Or is there a better way to get a field from the last row?

Thanks..

Bruce

Define "last row"? Tables are by definition unordered. The only way you
can get a "last" (or "first") row is to ORDER BY a field.

But, if you read the original message:
"WHERE primarykey - (SELECT MAX(primarykey) FROM table)"

This will ALWAYS produce just 1 record, so what order are you talking
about?

That will get the row with the highest primary key, true. But that is
not necessarily the last row.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstuck... (AT) attglobal (DOT) net
==================

Sorry. I should have said I wanted the "Highest" primary key...

So, is there some way I can find out the row with the "Highest"
primary key without knowing the name of the primary key.

This will give me the primary key name (SHOW INDEX FROM table) but I
don't know how to incorporate this information to my statement

SELECT myfield FROM table WHERE primarykey - (SELECT MAX(primarykey)
FROM table)

I also found the following which returns the primary key name, but
again I don't know how to incorporate the result into my SELECT.

SELECT k.COLUMN_NAME
FROM information_schema.table_constraints t
LEFT JOIN information_schema.key_column_usage k
USING(constraint_name,table_schema,table_name)
WHERE t.constraint_type='PRIMARY KEY'
AND t.table_schema=DATABASE()
AND t.table_name='mytable';

You would have to do a SHOW CREATE TABLE tblname and parse the result.
Bear in mind you may have multiple columns in a primary key, and the
primary key may or may not be numeric. And in some tables the concept
of "highest primary key" may be nonexistent - as in an n:n link table.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

#10

On Jan 2, 5:09*pm, Luuk <L... (AT) invalid (DOT) lan> wrote:

Quote:

On 02-01-11 22:31, bruce wrote:

On Jan 2, 3:36 pm, Jerry Stuckle<jstuck... (AT) attglobal (DOT) net> *wrote:
On 1/2/2011 3:17 PM, Luuk wrote:

On 02-01-11 19:50, Jerry Stuckle wrote:
On 1/2/2011 10:34 AM, bruce wrote:
Is there anyway, using a SELECT statement, I can determine what the
PRIMARY KEY is?

I want the select a field from the last row in a database. I'm using a
select statement
SELECT myfield FROM table WHERE primarykey - (SELECT MAX(primarykey)
FROM table)

Or is there a better way to get a field from the last row?

Thanks..

Bruce

Define "last row"? Tables are by definition unordered. The only way you
can get a "last" (or "first") row is to ORDER BY a field.

But, if you read the original message:
"WHERE primarykey - (SELECT MAX(primarykey) FROM table)"

This will ALWAYS produce just 1 record, so what order are you talking
about?

That will get the row with the highest primary key, true. *But that is
not necessarily the last row.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstuck... (AT) attglobal (DOT) net
==================

Sorry. I should have said I wanted the "Highest" primary key...

So, is there some way I can find out the row with the "Highest"
primary key without knowing the name of the primary key.

This will give me the primary key name (SHOW INDEX FROM table) but I
don't know how to incorporate this information to my statement

SELECT myfield FROM table WHERE primarykey - (SELECT MAX(primarykey)
FROM table)

I also found the following which returns the primary key name, but
again I don't know how to incorporate the result into my SELECT.

SELECT k.COLUMN_NAME
FROM information_schema.table_constraints t
LEFT JOIN information_schema.key_column_usage k
USING(constraint_name,table_schema,table_name)
WHERE t.constraint_type='PRIMARY KEY'
* * *AND t.table_schema=DATABASE()
* * *AND t.table_name='mytable';

The easiest way to do it is probably dynamically build a new SQL
statement with the info returned from your statement ( using PHP or
another language.)

--
Luuk

This is what I ended up doing...

Thanks for the response...

Bruce