select record of maxdate 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 2000 Forums

SQL Server Development (2000)

select record of maxdate in each date

Author

Topic

fan2005
Yak Posting Veteran

85 Posts

Posted - 2011-03-08 : 14:13:56

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 : 14:53:58

try this:

select * from #test touter where tid in
(select max(tid) from #test tinner where tinner.code = touter.code and tinner.tarikh = touter.tarikh
group by tinner.code,tarikh)
Order by touter.tid

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources