T-sql question

Question

Post reply

T-sql question

Jerry Tienter-162701

Say Hey Kid

Points: 694
More actions
December 28, 2007 at 2:44 pm

#185233

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.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

David.Poole SSC Guru Points: 76000 More actions · Answer 1

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

LinkedIn Profile

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

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)

karthik M One Orange Chip Points: 29139 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

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)