x

Help needed to complete my sql group by query

I have two tables

Table1:                    
RegNo  RegName                    
55     James                    
56     Frank                    
                    
Table 2:                    
RegNo FeedBack  Dated                    
55    Poor      17-01-2010                    
55    Good      18-01-2010                    
56    Very Good 17-01-2010                    
56    Average   19-01-2010                    

Now i need to show something like the following:

RegNo RegName FeedBack Dated
55 James Good 18-01-2010 (only latest record)
56 Frank Average 19-01-2010 (only latest record)
more ▼

asked Jan 20, 2010 at 03:02 AM in Default

Deepak Ghosh gravatar image

Deepak Ghosh
12 2 2 2

(comments are locked)
10|1200 characters needed characters left

1 answer: sort newest

declare @Table1 table (RegNo int, RegName varchar(10))
insert into @Table1
select 55,'James'
union select 56,'Frank'

declare @Table2 table (RegNo int, FeedBack varchar(10), Dated datetime)
insert into @Table2
select 55,'Poor','2010-01-17'
union select 55,'Good','2010-01-18'
union select 56,'Very Good', '2010-01-17'
union select 56,'Average', '2010-01-19'

select a.RegNo, RegName, FeedBack , Dated
from @Table1 a
join (select
row_number() over (partition by RegNo order by Dated desc) rownum,
RegNo, FeedBack , Dated
from @Table2) b
On a.RegNo = b.RegNo
where rownum = 1
more ▼

answered Jan 20, 2010 at 09:05 AM

RickD gravatar image

RickD
1.7k 1 1 4

Nice answer! I always for about things like PARTITION.
Jan 20, 2010 at 11:31 AM graz ♦
I use it loads where I am currently. We have loads of data that is much worse than this. That's what you get for working for telecoms.. ;)
Jan 20, 2010 at 11:39 AM RickD
Great.....thanks a ton for ur help RickD...you done it so easily...thanks again....
Jan 23, 2010 at 12:35 AM Deepak Ghosh
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1945
x36

asked: Jan 20, 2010 at 03:02 AM

Seen: 706 times

Last Updated: Jan 20, 2010 at 11:24 AM