getting first instance from one to many relation

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

SQL Server 2005 Forums

Transact-SQL (2005)

getting first instance from one to many relation

Author

Topic

devmet
Starting Member

5 Posts

Posted - 2009-01-12 : 11:18:09

Hi everybody,
I have two tables, events and orders table. An event can have multiple Orders and one order can also be related to multiple events. The problem is I only want one event related to one order regardless of that order being related to multiple events or just a single event. I have tried using "top 1" and "distinct" but it doesn't seem to work. Is there anyway I can accomplish this? Any help is greatly appriciated.

e.g

multiple event ids
OrderId EventId OrderId EventId
45856 45856 456856 86532
45856 84562 869562 86532
45856 745856 856238 86532

in case of multiple eventids, I just want the first event that is available. Also, I want this in the following context

select eventids from Order where orderid in (39722,1678584,712097,2442773)

should give first eventids for the given orders

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-12 : 11:27:19

[code]
select *
from
(
select row_number() over (partition by orderid order by eventid) as seq,*
)t
where seq=1
[/code]

savior faire
Posting Yak Master

194 Posts

Posted - 2009-01-12 : 11:27:25

Sounds like a design problem and not a sql command problem.
You need an associative table that "sits" between the orders and events table. I typed a longer reply a few minutes ago, but the site timed out on the posting of my reply.

Talk sense to a fool and he calls you foolish.

devmet
Starting Member

5 Posts

Posted - 2009-01-12 : 11:36:14

quote:

Originally posted by visakh16


select *
from
(
select row_number() over (partition by orderid order by eventid) as seq,*
)t
where seq=1

how would you incorporate this in a query if you want to display orderId and top eventid from the order table?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-12 : 12:08:55

[code]
select *
from
(
select row_number() over (partition by orderid order by eventid desc) as seq,*
from yourquery)t
where seq=1
[/code]

devmet
Starting Member

5 Posts

Posted - 2009-01-12 : 12:18:16

quote:

Originally posted by visakh16


select *
from
(
select row_number() over (partition by orderid order by eventid desc) as seq,*
from yourquery)t
where seq=1

This might work if it performs well enough. I'm using tank as shown in this article:

http://weblogs.sqlteam.com/jeffs/archive/2007/03/28/60146.aspx

Thanks for the input guys. I really appriciate it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-12 : 12:24:43

quote:
Originally posted by devmet

quote:
Originally posted by visakh16

select *
from
(
select row_number() over (partition by orderid order by eventid desc) as seq,*
from yourquery)t
where seq=1

This might work if it performs well enough. I'm using tank as shown in this article:

http://weblogs.sqlteam.com/jeffs/archive/2007/03/28/60146.aspx

Thanks for the input guys. I really appriciate it.

ok. so is your requirement to return all records with max eventid for orderid just in case it has multiple ones with same eventid?

devmet
Starting Member

5 Posts

Posted - 2009-01-12 : 14:07:17

quote:
Originally posted by visakh16

quote:
Originally posted by devmet

quote:
Originally posted by visakh16

select *
from
(
select row_number() over (partition by orderid order by eventid desc) as seq,*
from yourquery)t
where seq=1

This might work if it performs well enough. I'm using tank as shown in this article:

http://weblogs.sqlteam.com/jeffs/archive/2007/03/28/60146.aspx

Thanks for the input guys. I really appriciate it.

ok. so is your requirement to return all records with max eventid for orderid just in case it has multiple ones with same eventid?

the requirement is to return the max eventid if an order has multiple eventids associated with it.

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-01-12 : 14:14:46

quote:
Originally posted by devmet
the requirement is to return the max eventid if an order has multiple eventids associated with it.

the problem with rank is that if you have multiple records with the same max eventid, you'll see multiple records in the output of your join (since they all will have the same rank)

using row_number() will be safe if you want only one record in all the cases.

devmet
Starting Member

5 Posts

Posted - 2009-01-12 : 15:26:56

quote:
Originally posted by rohitkumar

quote:
Originally posted by devmet
the requirement is to return the max eventid if an order has multiple eventids associated with it.

the problem with rank is that if you have multiple records with the same max eventid, you'll see multiple records in the output of your join (since they all will have the same rank)

using row_number() will be safe if you want only one record in all the cases.

Ah!!! I see. Really thankful for your input.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-13 : 00:17:03

thats why i used row_number()

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources