help with a query - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

help with a query

Author

Topic

ssunny
Posting Yak Master

133 Posts

Posted - 2012-08-02 : 10:46:39

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

52326 Posts

Posted - 2012-08-02 : 10:54:15

[code]
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
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ssunny
Posting Yak Master

133 Posts

Posted - 2012-08-02 : 11:25:56

As always works like a charm and as always THANK YOU so much Visakh!

Visakhbhai Zindabad!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-02 : 11:37:16

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources