Hello, I'm using PostgreSQL, and have a table like this:

Code:

TABLE PartOrders
partNumber : integer
orderStatus : integer
orderTime : timestamp

So, there can be many rows with the same partNumber, because over time there will be reorders for that product. And every row will not necessarily have a unique orderTime, because orders can be inputted in batches. Although, those same times will probably correspond to different parts.

I'm trying to come up with an SQL query that will give me a result set with one row for each partNumber, where that row has the latest orderTime for the part. So, the latest order for each part.

I tried GROUP BY partNumber, but it complains that I haven't used an aggregate on orderStatus. And all the examples I find are for returning a single column, but I need to return every column in the table. See, there are more columns than I mentionned, but I've simplified the example.

Right now I'm getting the distinct partNumbers in one query, and looping over that, doing separate queries per partNumber, and then just taking the row with the latest orderTime. This is a total hack!

Can someone please help me? Or at least point me to some complex examples that might help? I don't do much SQL so any help would be appreciated.

Click to expand...