SELECT TOP question?

[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!

[ET]

Re: SELECT TOP question?

Well, the question is - can this be done in one SELECT TOP statement,
if yes, how?

[ET]

Re: SELECT TOP question?

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;

[ET]

Re: SELECT TOP question?

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;