[RESOLVED] Complex SQL Filter

[Quasar6]

I'm just after any thoughts or idea's about how I could approach this problem. It's got me stumped, but then again, I'm not an SQL wizard.

My table records the history of individual items as they change over the course of a project, so it may have three records of the same item with different "Upload numbers," and hense different stats.

My employer wants to be able to filter the table to show every item of a specific statistic (eg. All records where Area Number = ?).

Under normal circumstances this would be easy: "WHERE [Area] = ?".

But they don't want the table to be cluttered with the previous history items: they only want the latest items (those with the highest Upload Numbers). Add to this the fact that each upload may be of a small section of items, or of every item, or any selection of items they want, and you can see that the latest upload number may be different for each item.


In short, I need to filter by every record where [Area] equals user input, AND where the [Upload #] field is the highest of all records with the same [Item ID].

The end product will be displayed in a datagridview in C# Pro 2008 (but SQL is the same in VB, so that shouldn't matter). The database is 2003 Access, the connection OleDb.

Any ideas? Any help is appreciated.

Thanks,
Qu.

[Psyrus]

Maybe something like this?

SELECT ItemID, ItemDesc, MAX(UploadNum) as CurrentNum
FROM TableName
WHERE Area = UserArea
AND ItemID = UserItemID
GROUP BY ItemID, ItemDesc

[Quasar6]

Thanks for the suggestion Psyrus. I tried messing about with that format, as well as several other things (using the Last() function, and I tried subqueries as well), but never quite managed to return what I wanted.

I'm working on a workaround, where I loop through all the rows, extract the ID numbers of the ones I want, and generate an SQL string of the format WHERE Record.ID IN (x, y, z, ... etc). I then use this to generate an OleDbDataAdapter, and use that to fill the DataTable.

It's not a pretty solution, but it's functional and I've done it before.

Here's a gratituous frog: