Please assist with a query...

I have a table that can contain several entries for a given item id.
How would my select statement look if I wanted to query for:
- only the 'last' entry for 'each' id that meets certain criteria
I don't want a TOP x query, because I want the dataset to contain all
items whose 'last' entry meets certain criteria.Can you post your DLL? What is/are the key(s)? How can you tell which record
is last one?
"indee" <jeffderoche@.gmail.com> wrote in message
news:1125423719.220718.119790@.g47g2000cwa.googlegroups.com...
>I have a table that can contain several entries for a given item id.
> How would my select statement look if I wanted to query for:
> - only the 'last' entry for 'each' id that meets certain criteria
> I don't want a TOP x query, because I want the dataset to contain all
> items whose 'last' entry meets certain criteria.
>|||What criteria can we use to spot the last row for each group?
Example:
use northwind
go
-- last orders for each customer with an order
select
customerid, orderid
from
dbo.orders as a
where
orderdate = (select max(orderdate) from dbo.orders as b where
b.customerid = a.customerid)
go
AMB
"indee" wrote:

> I have a table that can contain several entries for a given item id.
> How would my select statement look if I wanted to query for:
> - only the 'last' entry for 'each' id that meets certain criteria
> I don't want a TOP x query, because I want the dataset to contain all
> items whose 'last' entry meets certain criteria.
>|||The primary key is an autonumber, however, there is an itemid field.
So, for example, here's what I want in plain english:
For each distinct item id, show me all whose last one entry (per the
autonumber) has status <> 1
So, I want the query to return all item ids whose last entry does not
have the status field set to 1.|||Can you try this (assume your autonumber is an Identity column):
select (whatever columns you have)
from YourTable T1
where T1.autonumber = (select max(T2.autonumber)
from YourTable T2
where T2.itemid = T1.itemid)
and T1.Status <> 1
"indee" <jeffderoche@.gmail.com> wrote in message
news:1125429028.896197.68730@.g14g2000cwa.googlegroups.com...
> The primary key is an autonumber, however, there is an itemid field.
> So, for example, here's what I want in plain english:
> For each distinct item id, show me all whose last one entry (per the
> autonumber) has status <> 1
> So, I want the query to return all item ids whose last entry does not
> have the status field set to 1.
>|||Try,
select
*
from
t1 as a
where
status != 1
and autonumber = (select max(b.autonumber) from t1 as b where b.itemid =
a.itemid)
AMB
"indee" wrote:

> The primary key is an autonumber, however, there is an itemid field.
> So, for example, here's what I want in plain english:
> For each distinct item id, show me all whose last one entry (per the
> autonumber) has status <> 1
> So, I want the query to return all item ids whose last entry does not
> have the status field set to 1.
>|||We're almost there, however, the query can return only the itemid
field, as it is a subquery...I really appreciate your help...
Here is the full query:
SELECT ID FROM vwAssets WHERE ID NOT IN(SELECT AssetID FROM
vwAssetValuation WHERE ActionEID IN (211, 212, 215) AND Stamp <= '" &
mCommon.FormatSQLDate(AsOf) & "') AND AssetID NOT IN(** QUERY GOES HERE
**)|||Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless.
THIS IS THE BASICS!! Have you ever bothered to do ANY studying
first?|||>> The primary key is an autonumber <<
Again, an autonumber cannot be a relationalo key BY DEFINITION. And
you still do not know that a field and columns are TOTALLY different
concepts.|||I see that you also use prefixes and data element names that violate
ISO-11179 data element rules. Does "vw-" mean Volkswagen?
Please stop programming until you learn the foundations. You are
dangerously ingnorant. Also you are going to get in Newsgroup is
kludges that will let you limp along until you have a disaster. Please
post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.