question

PeterH avatar image
PeterH asked

SELECT when there could be one record or two?

My table of bills contains these fields:

tblBills: PONumber Original_or_Revised (contains a code either 'O' or 'R') VendorName

For each PO number, the table could contain either one record or two.

Case A: There is an orginal & revised record for that PONumber.

Case B: There is only an original record for that PONumber.

I need a SELECT that return one record per PONumber along with the vendor name such that if a revised record exists, it will use that, if only an original record exists it will use that, as long as I get one record per PONumber in the result set.

I have tried various sub-queries, aggregates (create counts and choose from that) and self-joins, all of which work OK, but they seem overly complicated and I was wondering what is the true correct SQL-guru way of doing this?

Thanks.

queryselect
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

To be honest. a SQL guru probably wouldn't store two rows in a table to represent the same physical entity.
0 Likes 0 ·
Really? How should it be done? Application starts with a "default" bill, data is looked up from tables -- that is the "original"; sometimes it is accepted. Other times it's overridden so a new record is inserted with the revision in order to keep an audit trail of how it compares to the original. So, each PO could have one or two bill records; you'd put that in two separate tables? To what end?
0 Likes 0 ·

1 Answer

· Write an Answer
Squirrel 1 avatar image
Squirrel 1 answered
select *            
from            
(            
    select *, row_no = row_number() over (partition by PONumber order by revision desc)            
    from   tblBills            
) b            
where row_no = 1            
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.