x
login about faq Site discussion (meta-askssc)

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 '10 at 03:02 AM in Default

Deepak Ghosh gravatar image

Deepak Ghosh
12 1 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 '10 at 09:05 AM

RickD gravatar image

RickD
1.6k 1 1 4

Nice answer! I always for about things like PARTITION.

Jan 20 '10 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 '10 at 11:39 AM RickD

Great.....thanks a ton for ur help RickD...you done it so easily...thanks again....

Jan 23 '10 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.

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



Facebook logo Follow Ask SSC on Facebook
Find Ask SSC on Google+
linkedin logo Find us on LinkedIn

Topics:

x1834
x32

asked: Jan 20 '10 at 03:02 AM

Seen: 477 times

Last Updated: Jan 20 '10 at 11:24 AM

Copyright © 2002-2012 Simple Talk Publishing. All Rights Reserved. If you have any queries, please contact the site administrators.
Ask SQL Server Central is a community service provided by Red Gate.