Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
52326 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
52326 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
52326 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
52326 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  
 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.05 seconds. Powered By: Snitz Forums 2000