How will i get highest record for each entry.

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

Transact-SQL (2005)

How will i get highest record for each entry.

Author

Topic

srinig99
Starting Member

4 Posts

Posted - 2009-10-12 : 11:54:06

i have one table.Each time we add a row in that table.
per example i have fields like A B C D E F G

Values are like this in the table

1 2 3 4 sri open Y
1 2 3 4 bab close Y

5 6 7 8 barb open Y
5 6 7 8 sri ist Y
5 6 7 8 sweet close Y

Like that i have N number of records.

how will i select highest row in each set of records.

i have to select only these records from above data.
1 2 3 4 bab close Y
5 6 7 8 sweet close Y
....
.....

hope you understand my question

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-12 : 12:05:25

How can you decide what is the "highest" record?

No, you're never too old to Yak'n'Roll if you're too young to die.

srinig99
Starting Member

4 Posts

Posted - 2009-10-12 : 12:32:30

update time stamp field.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-12 : 12:35:48

There is no field like this in your example.

No, you're never too old to Yak'n'Roll if you're too young to die.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-10-12 : 13:01:38

Ok...try this...but like webfred pointed out..post some sample data that match your situation exactly.

select * from 
(
select *,row_number() over(partition by A,B,C,D order by <update_time_stamnp> desc) as seq from <urtable>
)t
where t.seq = 1

srinig99
Starting Member

4 Posts

Posted - 2009-10-12 : 13:17:16

same ple data

401661428 0642 698200 03 2009-10-09 15:49:00.000 CLOSE
401661428 0642 698200 03 2009-10-09 15:48:14.000 OPEN
400440299 04RA 608591 03 2009-10-09 15:36:31.000 CLOSE
400440299 0782 698200 01 2009-10-09 11:21:07.000 CLOSE
1470670339 0912 607592 03 2009-09-30 13:15:04.000 OPEN
479480075 1017 205401 04 2009-09-29 15:05:00.000 1ST
479480075 1015 607493 01 2009-09-23 12:08:16.000 OPEN
400440299 0782 698200 01 2009-09-23 11:17:00.000 OPEN
400440299 0782 698200 01 2009-09-23 11:06:46.000 OPEN
400440299 0771 698200 03 2009-08-27 09:30:13.000 OPEN
400440299 0782 698200 03 2009-08-26 13:06:59.000 OPEN

looks like your query works.

srinig99
Starting Member

4 Posts

Posted - 2009-10-12 : 13:21:20

Thanks its working vijayisonly.

web fred Sorry i didn't include update time stamp field in the example.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-10-12 : 13:22:12

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources