Maximum value should return from My query

Question

I have an out put using "Inner join , Left join" from different table, i have value as below

trans id  field1 field2 field 3 allocated  Trans status

1              a        a23   a32     user1       registered

1              a         a3      b       user2       Processed

1              c          c2      c45   user3        inprogress

using my query i returned above values but i need output as below

1             c            c2    c45      user3      inprogress.

I have audit table, N number of transaction entries are there in audit table, but report point of view i need last row of particular id and its value too..

above is one example but i have N number of records as mentioned above... how will i return these value.

please suggest.

Answer 1

Last row based on what? field1 (a,b,c,d,e....)? field3?

SELECT TOP 1 * FROM

(

SELECT *,ROW_NUMBER() OVER (ORDER BY field1 DESC) rn

FROM  tbl

) AS Der ORDER BY rn

---

Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

Blog : MS SQL Development and Optimization

Blog : Large scale of database and cleansing

Answer 2

> but report point of view i need last row of particular id and its value too.

Since there is no predefined order for rows in a table, I suggest you to expand about the meaning of "last row of particular id". In other words, what criteria can we use to identify the last row of a particular id?

---

AMB

Some guidelines for posting questions...

Answer 3

Last row of each entry  fields are varry from existing rows except Transid, While inserting the row in table, am inserting Unique Audit ID in respective table.

Answer 4

Hi Uri Demant,

Thanks for your reply Uri....

Your query will work if it is one transid in multiple rows.

but i have N number of different transid in Multiple rows and i need Last row of each trans id.

Kindly suggest.

Regards,

Sakthi.

Answer 5

Last row of each entry  fields are varry from existing rows except Transid, While inserting the row in table, am inserting Unique Audit ID in respective table.

is it what you mean by above sonditions:

select top 1 A.* from Table1 A inner join Table1 B where A.trans_id = B.trans_id and A.field1 <> B.field1

regards

joon

Answer 6

Try adding a Partition By clause to Uri's solution:

SELECT *,ROW_NUMBER() OVER (PARTITION BY TransId ORDER BY field1 DESC) rn