T-sql question
-
I have a table with purchase order info. There are several records with the same p.o number but kept in sequence by a 'posuf' that may be either a 1 or 2 or 3 or 4. How do I select a record with just a 'posuf' that = 4. I want the entire record but only if it has a 'posuf' that = 4 and not 3,2 or 1.
Thanks.
-
SELECT P.*
FROM PurchaseOrders AS P
INNER JOIN (
SELECT PurchaseOrderNumber, Max(Posuf) AS LastPosuf
FROM PurchaseOrders
GROUP BY PurchaseOrderNumber
) AS PM
ON P.PurchaseOrdernumber = PM.PurchaseOrdernumber
AND P.Posuf = PM.LastPosuf
-
Huh? I must be missing something there...If you only want them if they have "4" for "posuf", then I'm thinking that you need to say that somewhere in the code.
SELECT p.*
FROM PurchaseOrders AS p
P.Posuf = 4
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff,
Concur.
SELECT p.*
FROM PurchaseOrders AS p
P.Posuf in (4)
Will also work. Am i correct ? Is there any difference between your code and my code ?
karthik
-
Yes... it works.
No, no difference to the sever.
Yes... difference in readability but depends on what you're used to. I prefer the use of "=" to "IN" for single value criteria mosty because it's 3 characters less to type
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply