Testing Value & put Result in Query-URGENT HELP-

[igourine]

Please i need help

DRW N Action

400 RTO
400 Send to RT
400 RTO
400 RTO[/color]
500 RTO
500 Send to RT
500 RTO
500 Send to RT

i want to test the rows & get the latest Row Action for each DRW N (400 = RTO - 500=Send to RT)
Please note that i have many DRW N duplicated in my Table (400-500-600...etc)

 

[ByteMyzer]

Are DRW N and Action the only two fields in your table, or do you also have a date/time field or an Autoidentity field in your table?

 

[igourine]

no i have also date for each record
do you have any suggestion will be much appreciate

 

[ByteMyzer]

Providing that your table structure is something like the following:

MyTable
-------
DRW N (Drawing Number)
DRW Date (Action Date)
Action

Your SQL should look like the following (substitute highlighted text with actual table/field names):

SELECT T1.*
FROM [B][I]MyTable[/I][/B] AS T1
WHERE T1.[[B][I]DRW Date[/I][/B]] = (
    SELECT MAX(T2.[[B][I]DRW Date[/I][/B]])
    FROM [B][I]MyTable[/I][/B] AS T2
    WHERE T2.[[B][I]DRW N[/I][/B]] = T1.[[B][I]DRW N[/I][/B]]);

 

[igourine]

Dear Sir,

Many thanks it works but still did not gave me a result (Rows empty)
it shall give me

last update with other information for each DWG

 

[pbaldy]

Perhaps this similar method will work:

http://www.baldyweb.com/LastValue.htm

 

[ByteMyzer]

Perhaps this similar method will work:

http://www.baldyweb.com/LastValue.htm

I tested your method, and it achieves exactly the same results as does my method.


igourine, do you have some sample data that you can share with us, so that we may test it?