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.
| Author |
Topic |
|
RichardSteele
Posting Yak Master
160 Posts |
 Posted - 2008-04-07 : 06:41:41
|
| The following select retrieves multiple reoords for each i.number. How can I select just the first record for each i.number? SELECT i.number, i.desc, i.it_sdate, v.entry_date FROM itemsnum as I INNER JOIN Inventor as V ON SUBSTR(i.number,1,5)=v.catalog WHERE v.entry_date<ctod("04/01/06") AND i.it_sdate < ctod("04/01/06") order by number, it_sdate descThanks in advance! |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-04-07 : 06:50:22
|
Using SQL Server 2005 or SQL Server 2000?
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-04-07 : 06:50:38
|
| are you using sql 2005 or sql 2000? |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2008-04-07 : 06:50:46
|
| [code]SELECT i.number, i.desc, i.it_sdate, v.entry_date FROM itemsnum as Ijoin(Select number, max(it_sdate) as it_sdateFrom itemsnumgroup by number) I2on I.number = I2.number and I.it_sdate = I2.it_sdateINNER JOIN Inventor as V ON i.number = v.catalog + '%' WHERE v.entry_date<ctod("04/01/06") AND i.it_sdate < ctod("04/01/06") order by i.number, i.it_sdate desc[/code]Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
RichardSteele
Posting Yak Master
160 Posts |
Posted - 2008-04-07 : 13:37:44
|
I'm using sql Server 2005 in compatibility mode for 2000.quote:
Originally posted by Peso
Using SQL Server 2005 or SQL Server 2000?
E 12°55'05.25"N 56°04'39.16"
|
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
Retrieving only one record per item using a Select