using subquery to select top n records isn't working

lizymmm

Registered User.
Local time
Today, 15:17
Joined
Feb 15, 2011
Messages
11
The SQL statement I am using is -

SELECT [2010 chicks rung].bird_id, [2010 chicks rung].nest_rear, [2010 chicks rung].date, [2010 chicks rung].time, [2010 chicks rung].weight_g
FROM [2010 chicks rung]
WHERE [2010 chicks rung].bird_id IN
(SELECT TOP 4 bird_id
FROM [2010 chicks rung] AS Dupe
WHERE Dupe.bird_id = [2010 chicks rung].bird_id
ORDER BY Dupe.bird_id DESC)
ORDER BY [2010 chicks rung].bird_id, [2010 chicks rung].nest_rear, [2010 chicks rung].date, [2010 chicks rung].time, [2010 chicks rung].weight_g;

I have tried removing the ORDER BY in the subquery.

Basically it gives me all the results and not the top 4 as I wish. Any ideas as to how to correct this?

Thank you,

Lizy
 
Also field name ssuch as Date and Time are bad they are Access reserved words.
 
I cannot be 100% certain but I think the TOP is used in the main not the Sub query.

Brian
 
Your current query gives you the TOP 4 records per bird_id.

If all you want is the TOP 4 records as a whole then use this:
Code:
SELECT [COLOR=Red][B]TOP 4[/B][/COLOR] bird_id, nest_rear, [date], [time], weight_g
FROM [2010 chicks rung];
 
Thank you for all the replies!

I want the top 4 per bird_id so that I can then use the MOD 4 to obtain the 4th chronological record from each bird_id. I want to obtain the TOP 4 per bird_id initially as for each bird_id there may be 4, 5 or 6 records and the MOD 4 function would select every fourth record...not taking into account the bird_id.
Does this seem like a good approach to this problem?

I want the top 4 records for every bird_id (which I think is easiest to think of as a group from which the top four dates are required) not from all the bird_id's so putting the TOP function within the main query doesn't give what I need :s

I couldn't actually open that link but I hope the above description explains a bit better what I am after?

Cheers,

Lizy
 
I have just had a closer look at your problem and I can see that your table isn't normalised as there seems to be duplication. The query you were running would work on a junction table which yours isn't.

If you still want to go ahead with this, this would probably work:
Code:
SELECT bird_id, nest_rear, [date], [time], weight_g
FROM [2010 chicks rung]
WHERE [date] IN
    (
    SELECT TOP 4 Dupe.[date]
    FROM [2010 chicks rung] AS Dupe
    WHERE Dupe.bird_id = [2010 chicks rung].bird_id
    ORDER BY Dupe.bird_id, [date], [time]
    )
AND [time] IN
    (
    SELECT TOP 4 Dupe.[time]
    FROM [2010 chicks rung] AS Dupe
    WHERE Dupe.bird_id = [2010 chicks rung].bird_id
    ORDER BY Dupe.bird_id, Dupe.[date], Dupe.[time]
    )
ORDER BY bird_id, nest_rear, [date], [time], weight_g;
This was just a quick thought so there could be a more optimised version.

And of course this assumes that a combination of Date and Time per bird_id can yield unique records.
 
I would rank the data and then use a simple select on Rank query.
I'm not very good a air code but think the rank query would be like

Code:
SELECT x.[2010 chicks rung].bird_id, x.[2010 chicks rung].nest_rear, x.[2010 chicks rung].date, x.[2010 chicks rung].time, x.[2010 chicks rung].weight_g,
Select count(*)+1
	From [2010 chicks rung]
	Where [2010 chicks rung].bird_id=x.bird_id
	   And [2010 chicks rung].date > x.date As Rank
	       From [2010 chicks rung} as x
Order by x.birdid, x.date Desc


Brian
 

Users who are viewing this thread

Back
Top Bottom