Help Script Group by Order

Question

Help Script Group by Order

angelreynosog

Mr or Mrs. 500

Points: 595
More actions
June 13, 2016 at 1:16 pm

#308352

Hello dears,
Recently it made a change where I work on the database. There are currently 2 database and I have to make an inquiry . Follows , I have a purchase order made on the basis of data and History there are several scenarios presented . One is that the purchase order alone is in the historical database , the other scenario is that this in the historical and this in production , but difirente is that in the production agency column should leave as is in production , we had done a union and a group by, for what this doubled go, but as the production agency name is different to me twice . I appreciate your kindness in helping. Thank you!
Actualy
Agencia Cliente Nro Orden DATABASE
AOR SUIZA PRESIDENCIA DE LA REPUBLICAOSR0112898History
MCCANN ERICKSON SUIZA BEPENSA SUIZA, S.A. OSR0134075History
MCCANN ERICKSON SUIZA BEPENSA SUIZA, S.A. OSR0134075History
MCCANN ERICKSON SUIZA BEPENSA SUIZA, S.A. OSR0141125History
MCCANN ERICKSON SUIZA BEPENSA SUIZA, S.A. OSR0141125History
MCCANN ERICKSON SUIZA, S ABEPENSA SUIZA, SA OSR0134075Production
MCCANN ERICKSON SUIZA, S ABEPENSA SUIZA, SA OSR0134075Production
MCCANN ERICKSON SUIZA, S ABEPENSA SUIZA, SA OSR0141125Production
MCCANN ERICKSON SUIZA, S ABEPENSA SUIZA, SA OSR0141125Production
As the data must be
Agencia Cliente Nro Orden DATABASE
AOR SUIZA PRESIDENCIA DE LA REPUBLICAOSR0112898History
MCCANN ERICKSON SUIZA, S ABEPENSA SUIZA, SA OSR0134075Production
MCCANN ERICKSON SUIZA, S ABEPENSA SUIZA, SA OSR0134075Production
MCCANN ERICKSON SUIZA, S ABEPENSA SUIZA, SA OSR0141125Production
MCCANN ERICKSON SUIZA, S ABEPENSA SUIZA, SA OSR0141125Production
Thanths!!!!!!!

Viewing 2 posts - 1 through 1 (of 1 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

angelreynosog (6/13/2016)
Hello dears,
Recently it made a change where I work on the database. There are currently 2 database and I have to make an inquiry . Follows , I have a purchase order made ??on the basis of data and History there are several scenarios presented . One is that the purchase order alone is in the historical database , the other scenario is that this in the historical and this in production , but difirente is that in the production agency column should leave as is in production , we had done a union and a group by, for what this doubled go, but as the production agency name is different to me twice . I appreciate your kindness in helping. Thank you!
Actualy
Agencia Cliente Nro Orden DATABASE
AOR SUIZA PRESIDENCIA DE LA REPUBLICAOSR0112898History
MCCANN ERICKSON SUIZA BEPENSA SUIZA, S.A. OSR0134075History
MCCANN ERICKSON SUIZA BEPENSA SUIZA, S.A. OSR0134075History
MCCANN ERICKSON SUIZA BEPENSA SUIZA, S.A. OSR0141125History
MCCANN ERICKSON SUIZA BEPENSA SUIZA, S.A. OSR0141125History
MCCANN ERICKSON SUIZA, S ABEPENSA SUIZA, SA OSR0134075Production
MCCANN ERICKSON SUIZA, S ABEPENSA SUIZA, SA OSR0134075Production
MCCANN ERICKSON SUIZA, S ABEPENSA SUIZA, SA OSR0141125Production
MCCANN ERICKSON SUIZA, S ABEPENSA SUIZA, SA OSR0141125Production
As the data must be
Agencia Cliente Nro Orden DATABASE
AOR SUIZA PRESIDENCIA DE LA REPUBLICAOSR0112898History
MCCANN ERICKSON SUIZA, S ABEPENSA SUIZA, SA OSR0134075Production
MCCANN ERICKSON SUIZA, S ABEPENSA SUIZA, SA OSR0134075Production
MCCANN ERICKSON SUIZA, S ABEPENSA SUIZA, SA OSR0141125Production
MCCANN ERICKSON SUIZA, S ABEPENSA SUIZA, SA OSR0141125Production
Thanths!!!!!!!

Something like this might work.

--Generate sample data

CREATE TABLE #Test(

Agencia varchar(100),

Cliente varchar(100),

NroOrden char(10),

[DATABASE] varchar(100));

INSERT INTO #Test VALUES

('AOR SUIZA ', 'PRESIDENCIA DE LA REPUBLICA', 'OSR0112898', 'History '),

('MCCANN ERICKSON SUIZA ', 'BEPENSA SUIZA, S.A. ', 'OSR0134075', 'History '),

('MCCANN ERICKSON SUIZA ', 'BEPENSA SUIZA, S.A. ', 'OSR0141125', 'History '),

('MCCANN ERICKSON SUIZA, S A', 'BEPENSA SUIZA, SA ', 'OSR0134075', 'Production'),

('MCCANN ERICKSON SUIZA, S A', 'BEPENSA SUIZA, SA ', 'OSR0141125', 'Production'),

('MCCANN ERICKSON SUIZA, S A', 'BEPENSA SUIZA, SA ', 'OSR0141125', 'Production');

--Solution starts here

WITH CTE AS(

SELECT *, RANK() OVER(PARTITION BY NroOrden ORDER BY [DATABASE] DESC) rn

FROM #Test

)

SELECT Agencia ,

Cliente ,

NroOrden ,

[DATABASE]

FROM CTE

WHERE rn = 1;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2