Conditional insert based on MAX number of field

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

Conditional insert based on MAX number of field

Author

Topic

jigsatics
Starting Member

18 Posts

Posted - 2005-12-15 : 13:12:14

Hi!

I have a table called DB1 that contains this:

MID
IIN
NUM_EVENTS
DATE

MID, IIN and NUM_EVENTS are composite keys. and only NUM_EVENTS get incremented. All records start with NUM_EVENTS = 1.How can I create a query that only displays those records that only NUM_EVENTS = 1 meaning their still on the first stage of processing?

$3.99/yr .COM!
http://www.greatdomains4less.com

SamC
White Water Yakist

3467 Posts

Posted - 2005-12-15 : 13:37:15

Umm...

SELECT * FROM MyTable WHERE NUM_EVENTS = 1 ?

SamC
White Water Yakist

3467 Posts

Posted - 2005-12-15 : 13:41:17


SELECT M1.MID, M1.IIN, M1.NUM_EVENTS
  FROM MyTable M1
  INNER JOIN (
    SELECT MID, IIN, MAX(NUM_EVENTS) As MaxNUM_EVENTS
      FROM MyTable
      GROUP BY MID, IIN
  ) M2 ON M2.MID = M1.MIN AND M2.IIN = M1.IIN AND M2.MaxNUM_EVENTS = M1.NUM_EVENTS

  WHERE M1.NUM_EVENTS = 1

jigsatics
Starting Member

18 Posts

Posted - 2005-12-16 : 15:53:47

Thanks SamC. The second solution is what I need.

$3.99/yr .COM!
http://www.greatdomains4less.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources