selecting top n via a subquery isn't working

[Elizabeth Mitte]

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

[MikeTheBike]

Hi

I think I might try it likr this

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] INNER JOIN (SELECT TOP 4 bird_id FROM [2010 chicks rung] ORDER BY bird_id DESC) as Dupe ON Dupe.bird_id = [2010 chicks rung].bird_id
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;

??

MTB

[Elizabeth Mitte]

Thank you for replying.

The statement you suggested has given a bizarre output! For a single bird_id it has duplicated each record 6 times - there is now only one bird_id.

ie.

bird_id, date

1 01/01/2001
1 01/01/2001
1 01/01/2001
1 01/01/2001
1 01/01/2001
1 01/01/2001
1 01/01/2001
1 02/01/2001
1 02/01/2001
1 02/01/2001
1 02/01/2001
1 02/01/2001
1 02/01/2001
1 02/01/2001

I tried using a GROUP BY function within the subquery to no avail either. I am really stuck on this and will continue to search google.

Lizy

[MikeTheBike]

Without any knowledge of you data it is hard to know what you are expecting.

I assume the other three fields are also retuned also!!

MTB

[gershwyn]

I'm not sure the point of the WHERE clause in your subquery. You don't need to compare this to the main table at all, you're just looking for a list of the top 4.

Also, if there are multiple records for the same bird_id (which I suspect there are), your subquery might return the same ID more than once. If you're looking for all the data on the top 4 unique birds, you want to add the DISTINCT keyword as well.

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 DISTINCT TOP 4 bird_id
FROM [2010 chicks rung] AS Dupe
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;

This should return all the data for the records with a bird_id among the 4 highest in the table. If that's not what you need, please respond to clarify (and sample data always helps.)

[Elizabeth Mitte]

Thank you for the responses, what I am after is:

-the fourth chronological date (and the rest of data in this record) for each bird_id.

In some instances there are 4 dates for each bird_id, in some there are 5 or 6. For this reason I decided it would be best to collect the top four dates associated with each bird_id and then perform a MOD 4 function on this (possibly using an autonumber key).

I originally obtained my query (and subquery) from many different websites that say this query will get the top n records per group, in my case the bird_id is the group. I don't know why it isn't working or how to fix it despite much searching and fiddling around with the SQL...

I hope this makes a bit more sense? I need to end up with a date for every bird_id, not filter the bird_id's themselves.

[Rabbit]

You say that you're trying to get the 4th date but your query makes no reference to the date field. You are in fact filtering on bird ID which isn't actually doing any filtering at all since your bird ID is not unique and you are joining on bird ID.

Your subquery needs to take into account the date and your where clause needs to filter by date and not bird id.

[Elizabeth Mitte]

Thank you, you make a very obvious valid point!

My date field is not unique either, it is the combination of the bird_id and date within this that is unique...I'm now not sure how to proceed.

[ADezii]

@Elizabeth - I'm sure that I can create a relatively simple Code based solution to the problem, but SQL would definitely be the way to go. Should all else fail, doubt it with MikeTheBike, gershwyn, and Rabbit on the case, I'm sure that I can come up with something for you.

[Rabbit]

I would do something along the lines of

SELECT UniqueID, DateField
FROM Table1 AS x
WHERE DateField IN
  (SELECT TOP 4 DateField
   FROM Table1
   WHERE Table1.UniqueID = x.UniqueID
   ORDER BY DateField DESC)

However, as the code states, it's selecting the top 4. But your words seem to say that you don't actually want top 4. You say you want just the fourth date even though for some reason you're using top 4. In such a case, you would need to use a subquery ranking.

[ADezii]

@Rabbit - The way I read it is:
For each ID, return the 4th Record with all Fields, chronologically by Date. The combination of [ID] and [Date], as stated by the Op are Unique, so ties would not be a consideration. the Result Set would be 1 Record for each [ID] (the 4th chronologically per [ID]). Do you see it this way?

[Rabbit]

That's how I think the OP wants it. But her use of TOP 4 contradicts that assumption. I don't know if the miscommunication is with her choice of tactic or her choice of wording.

[ADezii]

@Elizabeth - before we go any further, I really think that we need to know exactly what the expected Results are. Given the Sample Data and the Results posted below, is this correct?

Primary_Key    Bird_ID    Date       Field3 Field4  Field5
1                1       1/15/2010    N/A    N/A    N/A
2                1       1/31/2010    N/A    N/A    N/A
3                1       3/23/2010    N/A    N/A    N/A
4                1       4/15/2010    N/A    N/A    N/A
5                1       6/19/2010    N/A    N/A    N/A
6                1       7/1 /2010    N/A    N/A    N/A
7                1      10/29/2010    N/A    N/A    N/A
8                1      11/23/2010    N/A    N/A    N/A
9                4       2/24/2010    N/A    N/A    N/A
10               4       5/29/2010    N/A    N/A    N/A
11               4       8/30/2010    N/A    N/A    N/A
12               4       9/2 /2010    N/A    N/A    N/A
13               4      12/21/2010    N/A    N/A    N/A
14               4      12/31/2010    N/A    N/A    N/A

Results (my interpretation):

Primary_Key    Bird_ID    Date       Field3 Field4  Field5
4                1       4/15/2010    N/A    N/A    N/A
12               4       9/2 /2010    N/A    N/A    N/A

[Elizabeth Mitte]

ADezii that is exactly what I am after! I have only been trying to get the top 4 as I couldn't think of another way of then selecting the 4th record for each...

I am still failing to get even the TOP 4

Thank you so much for all the replies

[ADezii]

@Elizabeth - now that we have a crystal clear understanding of exactly what the results should be, I'm sure that a solution will be forthcoming. SQL is definitely not my area of expertise, but I can always arrive at a Code-based solution as a worse care scenario, which I don't think will become necessary. Stay tuned to this channel! (LOL).

P.S. - Sounds like this should be a piece of cake for Rabbit, MikeTheBike, or gershwyn.

[Rabbit]

To get the N-th record of a set of records, I would use a ranking subquery. Kind of like this:

SELECT ID, DateField
FROM Table1 AS x
WHERE (
   SELECT Count(*)
   FROM Table1
   WHERE DateField <= x.DateField
      AND ID = x.ID
   ) = 4

[ADezii]

@Elizabeth - As expected, Rabbit nailed it. Substituting Field and Table Names based on the Sample Data in Post #13, and adding an ORDER BY Clause, the final SQL Statement would be:

SELECT [Primary_Key], [Bird_ID], [Date], [Field3], [Field4], [Field5]
FROM tblData AS x
WHERE ( 
   SELECT Count(*) 
   FROM tblData 
   WHERE [Date] <= x.[Date] 
      AND [Bird_ID] = x.[Bird_ID] 
   ) = 4
ORDER BY [Bird_ID];