Selecting relative data through subquery

[FunkyBunch]

Hello All,

I am currently creating a view to select the equipment id, the count of how many times it is in the table, the vehicle it was attached to before

Here is my sql:
Select access_equip_fw as ME_ID, count(access_equip_fw)"count(access_equip_Fw)",
(select top 1 vehicle_id_Fw from user_Defined20_Fw order by date_mounted_equipment_added_fw desc)
from user_Defined20_Fw
group by access_equip_fw


Here is my current output:
100 1 1171
1000 1 1171
1002 1 1171

What i want is:
100 1 5<vehicle attached to before this one>
1000 1 4<vehicle attached to before this one>
1002 1 95<vehicle attached to before this one>

 

[gmmastros]

Can you please show some sample data from the table? This will make it easier for us to help you.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[FunkyBunch]

Columns:
Equip ID, Date Equip Removed, Date Equip Added, Current Vehicle ID.

2 NULL 1999-12-01 00:00:00.000 4
3 NULL 2003-03-01 00:00:00.000 6055
4 NULL 1999-12-01 00:00:00.000 6
5 2007-09-06 00:00:00.000 1992-03-01 00:00:00.000 10
6 NULL 2001-01-01 00:00:00.000 11
7 NULL 2001-01-01 00:00:00.000 11
9 NULL 2001-01-01 00:00:00.000 11
12 NULL 2000-11-01 00:00:00.000 16

IF the me has a removal date it means it has been transfered to a different unit. I need to show the unit it was last attached to.

THanks a ton.