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 |
|
enniwesw
Starting Member
8 Posts |
 Posted - 2010-04-14 : 08:59:50
|
| Hello ladies and gentlemen,given a table with data like:Table 1 contains data like:-whs product dated mv_ref01 4510 2010/03/25 TS67552/101 4510 2010/04/08 TS6808301 4520 2010/03/23 TS6755201 4520 2010/04/12 TS6820001 4524 2010/03/23 TS6753401 4610 2010/03/19 TS6752301 4610 2010/03/19 TS6752301 4610 2010/03/26 CN31136801 4610 2010/04/06 TS64685/101 5202 2010/03/04 TS6683201 5202 2010/03/08 CN31126101 5202 2010/04/08 TS6808301 9131 2010/03/05 TS6675701 110004 2010/03/10 TS6601201 110004 2010/03/10 TS66012need a script which will extract a single mv_ref for a product having maximum date.... |
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-04-14 : 09:04:09
|
| [code]select * from(select *,row_number()over(partition by product order by dated desc) as rowid from yourtable)T where rowid=1[/code]PBUH |
 |
|
|
enniwesw
Starting Member
8 Posts |
Posted - 2010-04-14 : 09:24:21
|
| what i am looking for are products, and the most resent mv_ref…. |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-04-14 : 09:32:05
|
| Please post some sample expected output.PBUH |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-04-14 : 09:33:45
|
| [code]declare @tbl as table(whs int, product int, dated datetime, mv_ref varchar(50))insert into @tblselect 01 ,4510 ,'2010/03/25','TS67552/1'union allselect 01 ,4510 ,'2010/04/08',' TS68083' union allselect 01 ,4520 ,'2010/03/23',' TS67552'union allselect 01 ,4520 ,'2010/04/12',' TS68200'union allselect 01 ,4524 ,'2010/03/23',' TS67534'union allselect 01 ,4610 ,'2010/03/19',' TS67523'union allselect 01 ,4610 ,'2010/03/19',' TS67523'union allselect 01 ,4610 ,'2010/03/26',' CN311368'union allselect 01 ,4610 ,'2010/04/06',' TS64685/1'union allselect 01 ,5202 ,'2010/03/04',' TS66832'union allselect 01 ,5202 ,'2010/03/08',' CN311261'union allselect 01 ,5202 ,'2010/04/08',' TS68083'union allselect 01 ,9131 ,'2010/03/05',' TS66757'union allselect 01 ,110004 ,'2010/03/10',' TS66012'union allselect 01 ,110004 ,'2010/03/10',' TS66012'select * from @tblselect * from(select *,row_number()over(partition by product order by dated desc) as rowid from @tbl)T where rowid=1--output whs product dated mv_ref rowid----------- ----------- ----------------------- -------------------------------------------------- --------------------1 4510 2010-04-08 00:00:00.000 TS68083 11 4520 2010-04-12 00:00:00.000 TS68200 11 4524 2010-03-23 00:00:00.000 TS67534 11 4610 2010-04-06 00:00:00.000 TS64685/1 11 5202 2010-04-08 00:00:00.000 TS68083 11 9131 2010-03-05 00:00:00.000 TS66757 11 110004 2010-03-10 00:00:00.000 TS66012 1[/code]Whats wrong with the above output?PBUH |
|
|
|
enniwesw
Starting Member
8 Posts |
Posted - 2010-04-15 : 02:00:57
|
| Many thanks! that's what i am talking about... |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|
|
selectmax