Need Top 3 within 2 categories?

[raskew]

Hi -

My error. Go with ByteMyzer's solution (Post #17).

Bob

 

[ByteMyzer]

ions,

When using an IN clause with a Subquery, the arguments are one-to-one, not multi-to-multi, so the following portion of the SQL statement:

...WHERE [B][I]T1.Date & T1.Price[/I][/B] IN
(SELECT TOP 3 [B][I]T2.Date & T2.Price[/I][/B]...

concatenates the Date and Price fields to ensure that the Top 3 UNIQUE records are evaluated. I concatenated these two fields because you did not specify whether it is possible for a Parent/Type combination to have more than one price for the same date, and I was attempting to pre-empt duplicate subsets of data.

The Date-relevant portion with which you are concerned is addressed in the following portion of the SQL statement:

...ORDER BY [B][I]T2.Date DESC[/I][/B])...

...so the query should return the required result-set.

 

[ions]

Thank you,

I will study in detail tomorrow. It is interesting.

However, I don't understand how you can request top 3 for T2.Date & T2.Price (concatenated) How can you do a Top 3 on Date and Price concatenated? How does the computer process this?

I should of mentioned that the Date Field will have Time (Seconds) included. There will never be duplicate Dates+Time for a Parent / Type combination, hence,

>>whether it is possible for a Parent/Type combination to have more than one price for the same date, and I was attempting to pre-empt duplicate subsets of data.

is not possible.

Again thank you for everyone's assistance.

Peter.

 

[ions]

I believe this is the answer to what I want. Correct?


SELECT T1.*
FROM T1
WHERE T1.Date IN
(SELECT TOP 3 T2.Date
FROM MyTable T2
WHERE T2.Parent = T1.Parent
AND T2.Type = T1.Type
ORDER BY T2.Date DESC)
ORDER BY T1.Parent, T1.Type, T1.Date


Hmmm... not quite sure how it works still... I will need to see the recordset for the Second Select to fully understand it. When I look at it

Second Where should be.

Where
T2.Parent & T2.Type = T1.Parent & T1.type

I'll check in MS Access tomorrow.

Thank you.

 

[ByteMyzer]

...There will never be duplicate Dates+Time for a Parent / Type combination...

In that case, you can simply use:

SELECT T1.*
FROM [b][i]MyTable[/i][/b] T1
WHERE T1.[b][i]Date[/i][/b] IN
(SELECT TOP 3 T2.[b][i]Date[/i][/b]
 FROM [b][i]MyTable[/i][/b] T2
 WHERE T2.[b][i]Parent[/i][/b] = T1.[b][i]Parent[/i][/b]
 AND   T2.[b][i]Type[/i][/b] = T1.[b][i]Type[/i][/b]
 ORDER BY T2.[b][i]Date[/i][/b] DESC)
ORDER BY T1.[b][i]Parent[/i][/b], T1.[b][i]Type[/i][/b], T1.[b][i]Date[/i][/b]

 

[ions]

Thanks ByteMyzer,

I don't understand it but I am sure it works.

Why do you say MyTable T1? Shouldn't it be just T1?

Peter.

 

[ByteMyzer]

Thanks ByteMyzer,

I don't understand it but I am sure it works.

Why do you say MyTable T1? Shouldn't it be just T1?

Peter.

The intention is that you use the SQL statement as provided, substituting the italicized table/field names with the actual names.


The portion of the SQL Statement:

...FROM [B][I]MyTable[/I][/B] T1...

...tells the SQL interpreter to create an alias instance of table MyTable and name it T1. Likewise, the portion of the SQL Statement:

...FROM [B][I]MyTable[/I][/B] T2...

...tells the SQL interpreter to create another alias instance of table MyTable and name it T2. Thus, within a single SQL workspace, you are able to work with two instances of the same table, using different aliases to tell them apart.

 

[ions]

I guess you don't need the AS qualifier.

Thank you for all your help

 

[raskew]

Ions -

We're up to Post #28 -- you're about to set a record. Everytime someone tries to assist you, seems like you go 'duh-huh' and claim not to understand, are totally confused, or are unable to apply what you've been told.

May I suggest that Access databases may be beyond your field of comprehension and perhaps you need to look for a dfferent endeavor.

Bob

 

[ions]

Raskew I just totally lost respect for you.

Good Day.

PS. Bytemyzer your SQL worked perfectly