Easy T-SQL Question 2

[sdand]

I am sure this is a very easy question; I am just having a hard time figuring it out from the SQL online documentation. I need to write a query that lists only the last entered record, by employee ID, for the selected table. For example:

I have a table with the following fields:

ID - NameID - Date Entered.
1 – A1 – 1/1/01
2 – A1 – 2/5/01
3 – A2 – 3/2/02
4 – A3 – 4/19/02
5 – A1 – 5/5/02

I need the query to generate the following:

3 – A2 – 3/2/02
4 – A3 – 4/19/02
5 – A1 – 5/5/02

Thus, it would suppress ID#1 & 2, since they are not the last record for employee A1. How would I write the SQL statement to accomplish this? Thank you in advance.

Scott

 

[BobWman]

You would use the max function, and group by the date.

To bring back all your fields, use something like this:

select ID,NameID,DateEntered from MyTable
Where DateEntered In (
Select DateEntered =Max(DateEntered )
From MyTable
Group By DateEntered )

 

[bperry]

I like bobwman's suggestion, although it didn't quite work with the test data in my system. Probably because the inner subquery is not correlated to the outer query.

These two statements work on my system, try 'em if you care to. The first one is more efficient on my system, but depending on your setup and table sizes, one or the other may be better.

--------------------------------------
Select m1.[ID], m1.NameId, m1.DateEntered
from MyTable m1
Join (
Select NameID, MAX(DateEntered) as DateEntered
from MyTable
Group By NameId
) dt
ON m1.NameId = dt.NameId
AND m1.DateEntered = dt.DateEntered
Order by m1.[ID]
----------------------
Select * from MyTable m1
where [ID] =
(select Top 1 [ID] from MyTable m2
where m2.NameId = m1.NameId
Order by DateEntered DESC)
--------------------------------

 

[BobWman]

bperry is correct. I see my error.

I have used this type of statement grouping by an ID column, which is an autonumber column. If your ID column is an Autonumber column, or if you can correlate the latest date with the largest ID(the ID must be unique - that is the problem with my original query, the date isn't unique), then a similar statement should work.

For example:

select ID,NameID,DateEntered from MyTable
Where ID In (
Select ID =Max(ID)
From MyTable
Group By ID)

 

[BobWman]

I assume you have this resolved now, but here is one more sample(does not require Autonumber/Identity field):

Select M.ID, M.NameID, M.DateEntered from MyTable M
Where DateEntered In (
Select DateEntered =Max(DateEntered )
From MyTable
Where NameID = M.NameID)

NOTE: If you have 2 entries for the same person on the same date, they will both be returned.

 

[heidi88]

I have a dataset like this:

PO# Position Sequence# POQTY Invoice# InvoiceQTY

123 1 1 100 10 50
123 1 1 100 11 50

I need a result like this, so the PO qty won't be doubled even the PO was invoiced twice with half of the quantity on each.

PO# Position Sequence# POQTY Invoice# InvoiceQTY

123 1 1 100 10 50
123 1 1 0 11 50

Does anyone know how? Thanks a lot

 

[SQLBill]

Heidi,

Please start your own thread, don't 'hijack' someone else's, especially one that is 2-years old. Also, your question has nothing to do with the original post.

Thanks,

-SQLBill