select record of max date in each date

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 2008 Forums

Transact-SQL (2008)

select record of max date in each date

Author

Topic

fan2005
Yak Posting Veteran

85 Posts

Posted - 2011-03-08 : 09:32:36

hi
i have a table like this
-------------table---------------------

tid code account gardesh prebalance tarikh
5 119-2211-49 119-814-1937187-1 100000000000 204067583362 1388/06/21
6 119-2211-49 119-814-1937187-1 1806052792 304067583362 1388/07/01
7 119-2211-49 119-814-1937187-1 70000000000 305873636154 1388/07/01
8 119-2211-49 119-814-1937187-1 130000000000 375873636154 1388/07/01
9 119-2211-49 119-814-1937187-1 100000000000 605873636154 1388/07/14
10 119-2211-49 119-814-1937187-1 100000000000 705873636154 1388/07/14
17 121-4131-54 121-4-1937187-1 20000000000 0 1388/08/20
19 121-4131-54 121-4-1937187-1 -156134327 19999900000 1388/09/01
20 119-2211-49 119-814-1937187-1 8535848945 1610749516354 1388/09/01
21 121-4131-54 121-4-1937187-1 5000000000 19843765673 1388/09/30
22 121-4131-54 121-4-1937187-1 -2296333 24843765673 1388/09/30
23 119-2211-49 119-814-1937187-1 11976170575 1619285365299 1388/10/01

and for each day i want latest record
fro examle
output should be

tid code account gardesh prebalance tarikh
5 119-2211-49 119-814-1937187-1 100000000000 204067583362 1388/06/21
8 119-2211-49 119-814-1937187-1 130000000000 375873636154 1388/07/01
10 119-2211-49 119-814-1937187-1 100000000000 705873636154 1388/07/14
17 121-4131-54 121-4-1937187-1 20000000000 0 1388/08/20
20 119-2211-49 119-814-1937187-1 8535848945 1610749516354 1388/09/01
22 121-4131-54 121-4-1937187-1 -2296333 24843765673 1388/09/30
23 119-2211-49 119-814-1937187-1 11976170575 1619285365299 1388/10/01

in the date for example 1388/07/01 where i have more than one record i want latest record (recorde of max date in eache date)

thank you.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-03-08 : 10:58:32

It can be acheived by using the row_number function

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-03-08 : 11:02:41

Something to start with:

Select tid ,code ,account, gardesh, prebalance, tarikh from
(Select Row_number() Over (Partitioned by code,tarikh order by tid desc) as Srno,
* from yourtable ) as ST
Where ST.Srno =1

fan2005
Yak Posting Veteran

85 Posts

Posted - 2011-03-08 : 14:31:23

thank you very much
i m new to 2008 . how abput sql server2000 or pl sql

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources