Selecting Top 1 based on primary key

[rafeman]

Hi all,

I have a query below which returns a list of invoices from the PL_TRANSATIONS table. This was fine but I now need a field from the detail line tavle SL_PL_NL_DETAIL. I've added this in but I onviously now get mutiple rows where the invoice is multi lined. I need to amend the query so it only pulls back the 1st detail line per invoice. The analysis data I need is the same on each detail line.

PT_HEADER_REF is the unique invoice
The lowest DET_HEADER_KEY for an invoice line will be the first.

So I really want the first line by selecting the lowest header key per det_header_key,

How can I amend the query below to do this?

SELECT
PT_PRIMARY 'InvoiceID',
PT_HEADER_REF,
PT_NETT,
PT_VAT,
PT_GROSS,
isnull(SU_USRCHAR1,'') Approver1,
isnull(SU_USRCHAR2,'') Approver2,
isnull(SU_USRCHAR5,'') Approver3,
substring(DET_ANALYSIS,4,3) Department
FROM PL_TRANSACTIONS
left join PL_ACCOUNTS2 on SUCODE2 = PT_COPYSUPP
left join SL_PL_NL_DETAIL on DET_HEADER_KEY = PT_HEADER_KEY

 

[gmmastros]

What version of SQL Server are you using?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Try something like this:

SELECT 
PT_PRIMARY 'InvoiceID',
PT_HEADER_REF,
PT_NETT,
PT_VAT,
PT_GROSS,
isnull(SU_USRCHAR1,'') Approver1,
isnull(SU_USRCHAR2,'') Approver2,
isnull(SU_USRCHAR5,'') Approver3,
substring(DET_ANALYSIS,4,3) Department
FROM PL_TRANSACTIONS
left join PL_ACCOUNTS2 on SUCODE2 = PT_COPYSUPP
left join (Select PT_HEADER_KEY,
                  DET_ANALYSIS,
                  Row_Number() Over (Partition By PT_HEADER_KEY Order By DET_HEADER_KEY) As RowId
           From   SL_PL_NL_DETAIL
           ) As AliasName
           On AliasName.PT_HEADER_KEY = PL_TRANSACTIONS.DET_HEADER_KEY
           And AliasName.RowId = 1

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[rafeman]

Hi George, Sorry I haven't replied sooner, been busy.

Thanks very much, that appears to work after a minor tweak below;

SELECT
PT_PRIMARY 'InvoiceID',
PT_HEADER_REF,
PT_NETT,
PT_VAT,
PT_GROSS,
isnull(SU_USRCHAR1,'') Approver1,
isnull(SU_USRCHAR2,'') Approver2,
isnull(SU_USRCHAR5,'') Approver3,
substring(DET_ANALYSIS,4,3) Department
FROM PL_TRANSACTIONS
left join PL_ACCOUNTS2 on SUCODE2 = PT_COPYSUPP
left join (Select DET_HEADER_KEY,
DET_ANALYSIS,
Row_Number() Over (Partition By PT_HEADER_KEY Order By DET_HEADER_KEY) As RowId
From SL_PL_NL_DETAIL
inner join PL_TRANSACTIONS on PT_HEADER_KEY = DET_HEADER_KEY
) As AliasName
On AliasName.DET_HEADER_KEY = PL_TRANSACTIONS.PT_HEADER_KEY
And AliasName.RowId = 1