sql Help

I have a table like this in informix, User Contract ModifiedDate Person1 80545669 3/4/2008 6:41:08 PM Person2 80545669 3/4/2008 6:43:08 PM Person2 80545669 3/4/2008 6:43:08 PM Person3 80545669 3/4/2008 6:43:34 PM Person3 80545669 3/4/2008 6:43:34 PM Person4 80122222 3/11/2008 6:12:00 PM Person9 80122222 3/11/2008 6:11:00 PM Person5 80122222 3/11/2008 6:13:00 PM SELECT USER, contract, min(create_dt), FROM contract_audit WHERE ModifiedDate >= today-11 AND ModifiedDate < today-4 group by contract How should I change the sql statement, so that it will return the first people who modified the contract by contract the return rows shoule be Person1 80545669 3/4/2008 6:41:08 PM Person9 80122222 3/11/2008 6:11:00 PM

vchu wrote: > I have a table like this in informix, > > User Contract ModifiedDate > Person1 80545669 3/4/2008 6:41:08 PM > Person2 80545669 3/4/2008 6:43:08 PM > Person2 80545669 3/4/2008 6:43:08 PM > Person3 80545669 3/4/2008 6:43:34 PM > Person3 80545669 3/4/2008 6:43:34 PM > Person4 80122222 3/11/2008 6:12:00 PM > Person9 80122222 3/11/2008 6:11:00 PM > Person5 80122222 3/11/2008 6:13:00 PM > > > SELECT c.user, contract, create_dt -- did you mean ModifiedDate here? FROM contract_audit c WHERE Modifieddate = ( select min(modifieddate) from contract_audit c2 where c2.contract = c.contract and c2.modifieddate >= extend( (today-11), year to second) and c2.modifieddate < extend( (today-4), year to second) ); that will get you a unique first modified record as long as two people didn't modified the same contract in the same second. If two or more did so, and you MUST have only one row returned per contract, then you'll need another layer of subquery where the outer would return the minimum ROWID to compare to the ROWID in the outer query and the 'Modifydate = ' subquery would become the innermost query. OR just select the user with the minimum name since you can't tell which was first anyway. Art S. Kagel Oninit > SELECT USER, > contract, > min(create_dt), > FROM contract_audit > WHERE ModifiedDate >= today-11 > AND ModifiedDate < today-4 > group by contract> > How should I change the sql statement, so that it will return the first > people who modified the contract by contract > the return rows shoule be > > Person1 80545669 3/4/2008 6:41:08 PM > Person9 80122222 3/11/2008 6:11:00 PM > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > > =========================================================================================== > Please access the attached hyperlink for an important electronic communications disclaimer: > > http://www.oninit.com/home/disclaimer.php > > =========================================================================================== > > > =========================================================================================== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ===========================================================================================

Art, Thanks so much. You are genius Victor Art S. Kagel (Oninit) wrote: >> I have a table like this in informix, >> >[quoted text clipped - 9 lines] >> >> > >SELECT c.user, contract, create_dt -- did you mean ModifiedDate here? >FROM contract_audit c >WHERE Modifieddate = ( > select min(modifieddate) > from contract_audit c2 > where c2.contract = c.contract > and c2.modifieddate >= extend( (today-11), year to second) > and c2.modifieddate < extend( (today-4), year to second) >);> >that will get you a unique first modified record as long as two people >didn't modified the same contract in the same second. >If two or more did so, and you MUST have only one row returned per >contract, then you'll need another layer of subquery where the outer >would return the minimum ROWID to compare to the ROWID in the outer >query and the 'Modifydate = ' subquery would become the innermost >query. OR just select the user with the minimum name since you can't >tell which was first anyway. > >Art S. Kagel >Oninit > >> SELECT USER, >> contract,>[quoted text clipped - 24 lines] >> >> > >=========================================================================================== >Please access the attached hyperlink for an important electronic communications disclaimer: > >http://www.oninit.com/home/disclaimer.php > >===========================================================================================

Hi. I guess you should use a subquery, in order to first get the minimum modify date, just like this select * from contract_audit a where modified_date in (select min(modified_date) from contract_audit b where b.contract=a.contract)... and another filters I think that's going to work. I hope you find this useful. Omar Mu'oz --- vchu <u42118@uwe.iiug.org> wrote: > I have a table like this in informix, > > User Contract ModifiedDate > Person1 80545669 3/4/2008 6:41:08 PM > Person2 80545669 3/4/2008 6:43:08 PM > Person2 80545669 3/4/2008 6:43:08 PM > Person3 80545669 3/4/2008 6:43:34 PM > Person3 80545669 3/4/2008 6:43:34 PM > Person4 80122222 3/11/2008 6:12:00 PM > Person9 80122222 3/11/2008 6:11:00 PM > Person5 80122222 3/11/2008 6:13:00 PM > > > SELECT USER, > contract, > min(create_dt), > FROM contract_audit > WHERE ModifiedDate >= today-11 > AND ModifiedDate < today-4 > group by contract> > How should I change the sql statement, so that it > will return the first > people who modified the contract by contract > the return rows shoule be > > Person1 80545669 3/4/2008 6:41:08 PM > Person9 80122222 3/11/2008 6:11:00 PM > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > ____________________________________________________________________________________ Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ

Art S. Kagel (Oninit) wrote: > vchu wrote: >> I have a table like this in informix, >> >> User Contract ModifiedDate >> Person1 80545669 3/4/2008 6:41:08 PM >> Person2 80545669 3/4/2008 6:43:08 PM >> Person2 80545669 3/4/2008 6:43:08 PM >> Person3 80545669 3/4/2008 6:43:34 PM >> Person3 80545669 3/4/2008 6:43:34 PM >> Person4 80122222 3/11/2008 6:12:00 PM >> Person9 80122222 3/11/2008 6:11:00 PM >> Person5 80122222 3/11/2008 6:13:00 PM >> >> >> > > SELECT c.user, contract, create_dt -- did you mean ModifiedDate here? > FROM contract_audit c > WHERE Modifieddate = ( > select min(modifieddate) > from contract_audit c2 > where c2.contract = c.contract > and c2.modifieddate >= extend( (today-11), year to second) > and c2.modifieddate < extend( (today-4), year to second) > );> > > that will get you a unique first modified record as long as two people > didn't modified the same contract in the same second. If two or more did > so, and you MUST have only one row returned per contract, then you'll > need another layer of subquery where the outer would return the minimum > ROWID to compare to the ROWID in the outer query and the 'Modifydate = ' > subquery would become the innermost query. OR just select the user with > the minimum name since you can't tell which was first anyway. > Not relevant to the original question... but, any query that needs the rowid is wrong or is a consequence of a bad data model design. And you certainly can't assume that a lower ROWID represents a "first" row... It doesn't matter that we can create cases where it works... It's enough to have ONE case where it doesn't to invalidate it's use... -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

Fernando Nunes wrote: > Art S. Kagel (Oninit) wrote: > >> vchu wrote: >> >>> I have a table like this in informix, >>> >>> User Contract ModifiedDate >>> Person1 80545669 3/4/2008 6:41:08 PM >>> Person2 80545669 3/4/2008 6:43:08 PM >>> Person2 80545669 3/4/2008 6:43:08 PM >>> Person3 80545669 3/4/2008 6:43:34 PM >>> Person3 80545669 3/4/2008 6:43:34 PM >>> Person4 80122222 3/11/2008 6:12:00 PM >>> Person9 80122222 3/11/2008 6:11:00 PM >>> Person5 80122222 3/11/2008 6:13:00 PM >>> >>> >>> >>> >> SELECT c.user, contract, create_dt -- did you mean ModifiedDate here? >> FROM contract_audit c >> WHERE Modifieddate = ( >> select min(modifieddate) >> from contract_audit c2 >> where c2.contract = c.contract >> and c2.modifieddate >= extend( (today-11), year to second) >> and c2.modifieddate < extend( (today-4), year to second) >> );>> >> >> that will get you a unique first modified record as long as two people >> didn't modified the same contract in the same second. If two or more did >> so, and you MUST have only one row returned per contract, then you'll >> need another layer of subquery where the outer would return the minimum >> ROWID to compare to the ROWID in the outer query and the 'Modifydate = ' >> subquery would become the innermost query. OR just select the user with >> the minimum name since you can't tell which was first anyway. >> >> > > Not relevant to the original question... but, any query that needs the rowid is > wrong or is a consequence of a bad data model design. And you certainly can't > assume that a lower ROWID represents a "first" row... It doesn't matter that we > can create cases where it works... It's enough to have ONE case where it > doesn't to invalidate it's use... It WAS relevant to the original question, as stated, since there were two different updaters of the first contract that each had multiple entries with the same modifieddate. Since the OP wanted the earliest user to make an entry for each contract based on earliest modifieddate, and since the modifieddate column values MAY not be unique (I'd allowed that the duplicated datetime value was an unintended typo - hence my first query did not include the more complex filter) I was simply noting for the OP that IFF he wanted a single return for each contract and IF there were indeed duplicated modifieddate values, then he'd have to filter on SOMETHING else. YES! I agree, having to use ROWID as a filter is piss poor, but if the design is dependent on the modifieddate being unique - and it is unlikely to be unique for a contract because the design resolution is only to the second - then he as to use something. As to the ROWID actually being useful, welllllllllll it depends. If the table is ONLY added to and rows are never deleted, then a row added later will tend to have a higher rowid, but you are correct, you cannot depend on that. I wasn't REALLY depending on that accident of birth if you will to select the earlier of dups, but simply using it to select ONE of two or more dups. Nothing better could be suggested given the parameters of the problem as presented. I was going to explain that, but I got tired of typing and sent it. Why did you have a better idea? Actually, in the sample data above, only when the same updater updated more than once was the modifieddate repeated, so for the OP's purposes, selecting one of the two, if that modifieddate were the earliest in the date range, would be sufficient and definitive. Art S. Kagel Oninit =========================================================================================== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ===========================================================================================

Art S. Kagel (Oninit) wrote: > Fernando Nunes wrote: >> Art S. Kagel (Oninit) wrote: >> >>> vchu wrote: >>> >>>> I have a table like this in informix, >>>> >>>> User Contract ModifiedDate >>>> Person1 80545669 3/4/2008 6:41:08 PM Person2 80545669 >>>> 3/4/2008 6:43:08 PM Person2 80545669 3/4/2008 6:43:08 >>>> PM Person3 80545669 3/4/2008 6:43:34 PM Person3 >>>> 80545669 3/4/2008 6:43:34 PM Person4 80122222 3/11/2008 >>>> 6:12:00 PM Person9 80122222 3/11/2008 6:11:00 PM >>>> Person5 80122222 3/11/2008 6:13:00 PM >>>> >>>> >>> SELECT c.user, contract, create_dt -- did you mean ModifiedDate here? >>> FROM contract_audit c >>> WHERE Modifieddate = ( >>> select min(modifieddate) >>> from contract_audit c2 >>> where c2.contract = c.contract >>> and c2.modifieddate >= extend( (today-11), year to second) >>> and c2.modifieddate < extend( (today-4), year to second) >>> );>>> >>> >>> that will get you a unique first modified record as long as two >>> people didn't modified the same contract in the same second. If two >>> or more did so, and you MUST have only one row returned per contract, >>> then you'll need another layer of subquery where the outer would >>> return the minimum ROWID to compare to the ROWID in the outer query >>> and the 'Modifydate = ' subquery would become the innermost query. >>> OR just select the user with the minimum name since you can't tell >>> which was first anyway. >>> >>> >> >> Not relevant to the original question... but, any query that needs the >> rowid is wrong or is a consequence of a bad data model design. And you >> certainly can't assume that a lower ROWID represents a "first" row... >> It doesn't matter that we can create cases where it works... It's >> enough to have ONE case where it doesn't to invalidate it's use... > > It WAS relevant to the original question, as stated, since there were > two different updaters of the first contract that each had multiple > entries with the same modifieddate. Since the OP wanted the earliest I'm very sorry for my English... What I meant was that MY post was not relevant to the OP question... [...] > you cannot depend on that. I wasn't REALLY depending on that accident > of birth if you will to select the earlier of dups, but simply using it > to select ONE of two or more dups. Nothing better could be suggested > given the parameters of the problem as presented. I was going to > explain that, but I got tired of typing and sent it. Why did you have a > better idea? Nop... That why I wrote that if we find in a situation where we really need the ROWID, we're probably facing a design error... That would imply that we should change the design... :) Of course one thing is what we can write here, the other is what we can do on real systems... Believe me... I know :) I'm well aware that you know what are the implications of the rowid usage. But given just your post, forgetting your background, a more inexperienced user could find it a great idea to solve certain problems. The purpose of my post was to make clear that it's a bad idea. Sorry for the confusion. Regards, -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

Fernando Nunes wrote: > Art S. Kagel (Oninit) wrote: > >> Fernando Nunes wrote: >> >>> Art S. Kagel (Oninit) wrote: >>> >>> >>>> vchu wrote: >>>> >>>> >>>>> I have a table like this in informix, >>>>> >>>>> User Contract ModifiedDate >>>>> Person1 80545669 3/4/2008 6:41:08 PM Person2 80545669 >>>>> 3/4/2008 6:43:08 PM Person2 80545669 3/4/2008 6:43:08 >>>>> PM Person3 80545669 3/4/2008 6:43:34 PM Person3 >>>>> 80545669 3/4/2008 6:43:34 PM Person4 80122222 3/11/2008 >>>>> 6:12:00 PM Person9 80122222 3/11/2008 6:11:00 PM >>>>> Person5 80122222 3/11/2008 6:13:00 PM >>>>> >>>>> >>>>> >>>> SELECT c.user, contract, create_dt -- did you mean ModifiedDate here? >>>> FROM contract_audit c >>>> WHERE Modifieddate = ( >>>> select min(modifieddate) >>>> from contract_audit c2 >>>> where c2.contract = c.contract >>>> and c2.modifieddate >= extend( (today-11), year to second) >>>> and c2.modifieddate < extend( (today-4), year to second) >>>> );>>>> >>>> >>>> that will get you a unique first modified record as long as two >>>> people didn't modified the same contract in the same second. If two >>>> or more did so, and you MUST have only one row returned per contract, >>>> then you'll need another layer of subquery where the outer would >>>> return the minimum ROWID to compare to the ROWID in the outer query >>>> and the 'Modifydate = ' subquery would become the innermost query. >>>> OR just select the user with the minimum name since you can't tell >>>> which was first anyway. >>>> >>>> >>>> >>> Not relevant to the original question... but, any query that needs the >>> rowid is wrong or is a consequence of a bad data model design. And you >>> certainly can't assume that a lower ROWID represents a "first" row... >>> It doesn't matter that we can create cases where it works... It's >>> enough to have ONE case where it doesn't to invalidate it's use... >>> >> It WAS relevant to the original question, as stated, since there were >> two different updaters of the first contract that each had multiple >> entries with the same modifieddate. Since the OP wanted the earliest >> > > I'm very sorry for my English... What I meant was that MY post was not relevant > to the OP question... > No problem. Careful rereading and I get your meaning. > [...] > >> you cannot depend on that. I wasn't REALLY depending on that accident >> of birth if you will to select the earlier of dups, but simply using it >> to select ONE of two or more dups. Nothing better could be suggested >> given the parameters of the problem as presented. I was going to >> explain that, but I got tired of typing and sent it. Why did you have a >> better idea? >> Eek! You didn't deserve that last snap. My apologies to you Fernando.. > > Nop... That why I wrote that if we find in a situation where we really need the > ROWID, we're probably facing a design error... That would imply that we should > change the design... :) Of course one thing is what we can write here, the > other is what we can do on real systems... Believe me... I know :) > > I'm well aware that you know what are the implications of the rowid usage. But > given just your post, forgetting your background, a more inexperienced user > could find it a great idea to solve certain problems. The purpose of my post > was to make clear that it's a bad idea. Sorry for the confusion. > On that point we clearly agree. ;-) Art Oninit =========================================================================================== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ===========================================================================================