Group by Question

[Mickbw]

The following code works correctly but I would like to group the results by the field transaction_id and only the transaction_id.

Select Distinct cast(substring(Ledger.Transaction_ID,3,11) as int ) as checknumber, 
Ledger.Transaction_ID  as TransactionID,  
Ledger.agency,  
rtrim(ledger.ref_trans_id) as pv , 
Vendors.vendor_code as vendorid,
Vendors.vendor_name as vendor,
Vendors.attention_line as attn,
Vendors.vendor_address_line1 as addr1,
Vendors.vendor_address_line2 as addr2,
Vendors.vendor_address_line3 as addr3,			Vendors.city,Vendors.state,Vendors.zip,
rtrim(Vendors.phone_area) +  rtrim(Vendors.phone_number) as Phone, 
Vendors.contact,
Vendors.last_payment_date as LastPay, 
Vendors.create_change_date as LastUpdate, 
Vendors.comment,
Vendors.id_type as Vendortype, 
Vendors.id_number as VendorNumber, 
Vendors.vend_elec_pay_ind as Epay
from dbo.ifs_table_clrled Ledger
left outer join dbo.ifs_table_vend Vendors
on Ledger.Vendor_code = Vendors.Vendor_code
where Ledger.fisc_year = '2004'  and Vendors.fisc_year = '2004' AND Ledger.bank_account_code = '54'
AND Ledger.Transaction_ID = 'AD00001496868'

In many cases the only field that shows any different value is the PV field. Is there a way to do the query showing only the first value from the PV field.

Michael Brennan-White
New Hampshire Treasury Department

http://www.nh.gov/treasury

 

[swampBoogie]

the first value from the PV field.

There is no concept of first in a RDBMS. You can use MAX or MIN to get the highest or owest value of PV.

Select cast(substring(Ledger.Transaction_ID,3,11) as int ) as checknumber,
Ledger.Transaction_ID  as TransactionID,  
Ledger.agency,  
max(rtrim(ledger.ref_trans_id)) as pv ,
Vendors.vendor_code as vendorid,
Vendors.vendor_name as vendor,
Vendors.attention_line as attn,
Vendors.vendor_address_line1 as addr1,
Vendors.vendor_address_line2 as addr2,
Vendors.vendor_address_line3 as addr3,            Vendors.city,Vendors.state,Vendors.zip,
rtrim(Vendors.phone_area) +  rtrim(Vendors.phone_number) as Phone,
Vendors.contact,
Vendors.last_payment_date as LastPay,
Vendors.create_change_date as LastUpdate,
Vendors.comment,
Vendors.id_type as Vendortype,
Vendors.id_number as VendorNumber,
Vendors.vend_elec_pay_ind as Epay
from dbo.ifs_table_clrled Ledger
left outer join dbo.ifs_table_vend Vendors
on Ledger.Vendor_code = Vendors.Vendor_code
where Ledger.fisc_year = '2004'  and Vendors.fisc_year = '2004' AND Ledger.bank_account_code = '54'
AND Ledger.Transaction_ID = 'AD00001496868'
group by
cast(substring(Ledger.Transaction_ID,3,11) as int ),
Ledger.Transaction_ID,  
Ledger.agency,  
Vendors.vendor_code,
Vendors.vendor_name,
Vendors.attention_line,
Vendors.vendor_address_line1,
Vendors.vendor_address_line2,
Vendors.vendor_address_line3,            Vendors.city,Vendors.state,Vendors.zip,
rtrim(Vendors.phone_area) +  rtrim(Vendors.phone_number),
Vendors.contact,
Vendors.last_payment_date,
Vendors.create_change_date,
Vendors.comment,
Vendors.id_type ,
Vendors.id_number,
Vendors.vend_elec_pay_ind