1:M and MAX - SQL Server Forums

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

All Forums

General SQL Server Forums

New to SQL Server Programming

1:M and MAX

Author

Topic

Informer30
Starting Member

26 Posts

Posted - 2013-10-10 : 06:33:58

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

52326 Posts

Posted - 2013-10-10 : 08:26:42

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

Informer30
Starting Member

26 Posts

Posted - 2013-10-10 : 09:21:22

hi

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

I am looking to have rows

1152323
1152324
1152325

back in my results...

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-10 : 09:33:06

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

Informer30
Starting Member

26 Posts

Posted - 2013-10-10 : 09:56:17

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-10 : 10:28:28

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

Informer30
Starting Member

26 Posts

Posted - 2013-10-10 : 11:28:41

Just doing a few tests and going to request user testing - will advise shortly...

Thanks

Informer30
Starting Member

26 Posts

Posted - 2013-10-14 : 09:49:44

All working for now thanks....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-14 : 09:51:51

ok...cool

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources