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 |
|
bunny28
Starting Member
13 Posts |
 Posted - 2009-02-12 : 21:31:40
|
| Hi all,I have a history table which doesnt have any primary key or indexes,when ever there is an update in the master table i will pick the previuos record from the master table and insert history table along with change_date now my requirement is i want a query that can give me which is the last updated row according to change_date for the all disticnt rows.i hope i am clear please mail me if any info. is required.i tried using order by change_date desc and group by name but it is not working .thanks is advance |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
bunny28
Starting Member
13 Posts |
Posted - 2009-02-12 : 21:56:01
|
| Thanks for the reply i cannot go for a primary key bcos as i said i am saving history data here its like when ever there is update in the master i need to save a copy of old one in history (where in master my name column is primary) and maintain the updted at master.and now what user wants is they just want have a look at the records just to compare both the reports and know what was their previuos update.so i need the latest record according to change_date column in my table for distinct so that i can generate a report. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-13 : 01:30:25
|
did you meant this?SELECT columns...FROM(SELECT ROW_NUMBER() OVER(PARTITION BY masterpk order by change_date desc) as seq, other columns...FROM HistoryTable)hWHERE Seq=1 |
|
|
|
bunny28
Starting Member
13 Posts |
Posted - 2009-03-09 : 05:39:38
|
| thanks visakh sorry for the delay i was on leave.I tried the query its working, i never used partition this is my first time.I am getting the result but i dont understand it, would you mind explaining this to me.Thank you very much |
|
|
|
ddramireddy
Yak Posting Veteran
81 Posts |
Posted - 2009-03-09 : 07:58:06
|
| Read the Last Post by xpscodes the below thread. I think You can understand thishttp://forums.asp.net/t/1235491.aspx |
|
|
|
ddramireddy
Yak Posting Veteran
81 Posts |
Posted - 2009-03-09 : 07:58:06
|
| Read the Last Post by xpscodes the below thread. I think You can understand thishttp://forums.asp.net/t/1235491.aspx |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-10 : 03:33:26
|
quote:
Originally posted by bunny28
thanks visakh sorry for the delay i was on leave.I tried the query its working, i never used partition this is my first time.I am getting the result but i dont understand it, would you mind explaining this to me.Thank you very much
what partition does is group records as sets by means of masterpk value and then take the one with latest value of change_date field. so in effect you will get one record each for masterpk value which contain latest value for change_date |
|
|
|
|
|
|
Reg:query