SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 1:M and MAX
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Informer30
Starting Member

26 Posts

Posted - 10/10/2013 :  06:33:58  Show Profile  Reply with Quote
Hi All,

Please can anyone help with the below query...

col 1 is the pk for this table called Conditions - this table is related to contract.

Col 1 - PK Contract Type Type ID
973300 711917 C 30
973301 711917 C 32
973302 711917 C 31
1152323 711917 C 30
1152324 711917 C 31
1152325 711917 C 32


A contract can many conditions so 1:M

Col 2 is the contract reference and the linking join to contract

Now - a condition related to an contract can have many re-trys and the
causes the pk to increment. As you can see there are three conditions
related to first attempt and then another three conditions.

I want to create an ouput which just reflects the latest conditions when
joining back to contract - Is this possible?

I have requested to application providers to provide flag, but this will take some time...

Many Thanks for your help...

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/10/2013 :  08:26:42  Show Profile  Reply with Quote
so in the above what should be your output?
try below and see if its what you're after

SELECT Col1,Contract,type,TypeID
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY Contract,TypeID ORDER BY Col1 DESC) AS Seq,*
FROM Conditions
)t
WHERE seq=1


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Informer30
Starting Member

26 Posts

Posted - 10/10/2013 :  09:21:22  Show Profile  Reply with Quote
hi

thanks for the code, however this has not worked...

I am looking to have rows

1152323
1152324
1152325

back in my results...

Thanks
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/10/2013 :  09:33:06  Show Profile  Reply with Quote
it will give you correct output for sample data posted. Please post proper data if your scenario is different from above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Informer30
Starting Member

26 Posts

Posted - 10/10/2013 :  09:56:17  Show Profile  Reply with Quote
Hi,

the data is correct, I have changed the code

WHERE seq IN ('1', '2','3')

I have run the query and seems all ok so far.

I will test further and advise...

Thanks
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/10/2013 :  10:28:28  Show Profile  Reply with Quote
Hmm..Can you show some more data. I guess you've some other info too which you've not stated above.



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Informer30
Starting Member

26 Posts

Posted - 10/10/2013 :  11:28:41  Show Profile  Reply with Quote
Just doing a few tests and going to request user testing - will advise shortly...

Thanks
Go to Top of Page

Informer30
Starting Member

26 Posts

Posted - 10/14/2013 :  09:49:44  Show Profile  Reply with Quote
All working for now thanks....
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/14/2013 :  09:51:51  Show Profile  Reply with Quote
ok...cool

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000