SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 help with a query		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ssunny
Posting Yak Master

USA
130 Posts
Posted - 08/02/2012 :  10:46:39  Show Profile  Reply with Quote
Hello All,
I need your help to write a query. Below is my table structure and expected output.

test

aid aname

100 a
200 b
300 c


test1

aid eid

100 1000
100 2000
100 3000
100 4000
100 5000
200 1300
200 1400
200 1500
300 1800


test2

eid ename

1000 'aaa'
1300 'bbb'
1400 'ccc'
1500 'ddd'
1800 'eee'
2000 'fff'
3000 'ggg'
4000 'hhh'
5000 'iii'

expected output

aid aname eid ename
100 a 5000 'iii'
100 a 4000 'hhh'
100 a 3000 'ggg'
200 b 1500 'ddd'
200 b 1400 'ccc'
200 b 1300 'bbb'
300 c 1800 'eee'


SO I want top 3 eid,e.name (order by eid desc) for a given aid. test1 is a junction table which joins aid and eid.

Thanks

visakh16
Very Important crosS Applying yaK Herder

India
48076 Posts
Posted - 08/02/2012 :  10:54:15  Show Profile  Reply with Quote 

SELECT t.aid,t.aname,t2.ename
FROM test t
INNER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY aid ORDER BY eid DESC) AS Seq,*
FROM test1)t1
ON t1.aid = t.aid
AND t1.Seq <=3
INNER JOIN test2 t2
ON t1.eid = t2.eid


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

ssunny
Posting Yak Master

USA
130 Posts
Posted - 08/02/2012 :  11:25:56  Show Profile  Reply with Quote
As always works like a charm and as always THANK YOU so much Visakh!

Visakhbhai Zindabad!!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
48076 Posts
Posted - 08/02/2012 :  11:37:16  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000