By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,466 Members | 1,202 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,466 IT Pros & Developers. It's quick & easy.

SELECT TOP question?

P: n/a
ET
Hello...

I have two tables, first is Location
ID location
-------------------------------
1 London
2 Moscow
3 Beijing
4 New York
5 Alexandria
Second table is Names table, and ID field in Names table is foreign key
to ID field in first table - Location (above)...
t2ID ID name2
----------------------------------------------
1 1 aa
2 1 ab
3 2 bb
4 2 bc
5 3 c
6 4 dd
7 4 ddd
8 4 ddddd
9 5 ee
10 5 eeee
I would like to pull top t2ID records from Names table, based on ID in
Names...
In other words, if for ID = 1 we have two records:
t2ID - ID
-------------
1 - 1
2 - 1

I'll need to pull t2ID = 2... the same for all other IDs in Names
table...
Final result set should be like this:
t2ID ID name2
----------------------------------------------
2 1 ab
4 2 bc
5 3 c
8 4 ddddd
10 5 eeee
Thank you...

p.s.
it will be ok even if only top t2ID records will be returned, like:
t2ID
------
2
4
5
8
10

Thank you!

Nov 23 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
ET
Well, the question is - can this be done in one SELECT TOP statement,
if yes, how?

Nov 23 '05 #2

P: n/a
ET
Great... I found the solution... instead of "TOP 1" just use "TOP
10000" where number after TOP clause is any number greater then number
of records (current or expected in future) in table...
SELECT *
FROM [SELECT TOP 1000000
MAX(Table2.t2ID) AS MAXID,
Table2.ID
FROM Table2
GROUP BY Table2.ID
ORDER BY MAX(Table2.t2ID) DESC
]. AS [Table2_Alias]
ORDER BY Table2.ID;

Nov 23 '05 #3

P: n/a
ET
Or this solution:
SELECT Table2.t2ID,
Table2.ID,
Table2.name2
FROM Table2
WHERE t2ID IN
(
SELECT TOP 1000000
MAX(Table2.t2ID) AS MAXID
FROM Table2
GROUP BY Table2.ID
ORDER BY MAX(Table2.t2ID) ASC
)
ORDER BY Table2.ID ASC;

Nov 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.