difficult(for me) query help

[dadamssg87]

I have a table products: id, username, code, price, description, and iteration. The "code" column is an alphanumeric string unique to that product. Instead of having a form to update that row, i was thinking i could add an "iteration" column and create a whole new row with the updated information. This  way i could track how the product changes and progresses over time. The "iteration" would be an integer and increase +1 every time that product gets updated.

 

Could someone help me write a query that selects the latest iteration of all products with a certain username? I have no idea how to incorporate the code and iteration together.

 

Any help would be MUCH appreciated.

 

[awjudd]

Table structure that you are planning on using would be required to help with this.

 

~awjudd

[dadamssg87]

sorry,

 

id: bigint (11)

username: bigint(11)

code: varchar(50) latin1_bin

title: varchar(150)

description: text

iteration: int(11)

[awjudd]

SELECT id, username, code, title, description, iteration
FROM table t
where iteration = ( SELECT MAX(iteration) FROM table WHERE username = t.username )

 

Something like that?

[dadamssg87]

Products

IdUsernamecodepriceiteration

 

14xyz1.990

 

24xyz4.991

 

34xyz2.992

 

47asgprice0

 

56ewrprice0

 

64123price0

 

SELECT id, username, code, price, iteration
FROM Products
where iteration = ( SELECT MAX(iteration) FROM table WHERE username = Products.username )

 

The above code would produce the following? I'm not sure what the "t" represents in your query and also don't see how your query gets the max() iteration of each product code.

 

 

IdUsernamecodepriceiteration

34xyz2.992

64123price0

[dadamssg87]

*

 

SELECT id, username, code, price, iteration
FROM Products
where iteration = ( SELECT MAX(iteration) FROM Products WHERE username = '4' )