Query to find smallest value

[squallleonhart]

Hello,
I have a query that looks at a couple of tables that have renewal dates for group ID's. Each renewal date is separated into blocks of group ID's, these blocks are given a block ID. My query looks at the tables and based on the renewal date chosen returns the last two blocks, along with the corresponding data, for the groups who have that renewal date. Here is the code that may make more sense:

SELECT [Groups by Renewal Date].RenewalRateEffectiveDate, dbo_BBB_GroupData.blockID, dbo_BBB_GroupData.groupID, dbo_BBB_GroupData.rateChange, dbo_BBB_GroupData.totalCensusEmployeesMedical
FROM [Groups by Renewal Date] INNER JOIN dbo_BBB_GroupData ON [Groups by Renewal Date].groupID = dbo_BBB_GroupData.groupID
WHERE ((([Groups by Renewal Date].RenewalRateEffectiveDate)=[mm/dd/yyyy]) AND ((dbo_BBB_GroupData.blockID) In (SELECT TOP 2 T.blockID FROM dbo_BBB_GroupData T WHERE T.groupID = dbo_BBB_GroupData.groupID ORDER BY T.blockID Desc)));

I would like to create another query that returns the data for the smallest block ID, in other words filtering out the max block ID from the previous query results. I thought this would be really simple, but the code below does not filter anything out so the results of the two queries are the same.

SELECT Last(Last2Blocks.groupID) AS LastOfgroupID, Last2Blocks.blockID, Last2Blocks.rateChange, Last2Blocks.totalCensusEmployeesMedical
FROM Last2Blocks
GROUP BY Last2Blocks.blockID, Last2Blocks.rateChange, Last2Blocks.totalCensusEmployeesMedical;

Any help would be greatly appreciated!

 

[xenou]

If the second query is based on the first query (which is to say, it queries the first query where the first query is named Last2Blocks) then yes you would have a problem.

The first query selects only the top 2 BlockIDs because you have a where condition that creates that condition.

So anything that queries that query can only return the top 2 BlockIDs no matter how you slice or dice it.

It looks like your second query should be the same as the first query, but change the order by in the subquery in your where clause to ascending order instead of descending order.

 

[squallleonhart]

You are right, I'm trying to use the second query as a filter on the first query. From the 2 block ID's returned from the first query I want only the smallest.
Perhaps a better question is can I create a query that returns only the second largest block ID or the only block ID if a group is a member of only one block?

 

[xenou]

I see, I misunderstood. I thought you wanted the smallest absolutely. If you only want the smallest which is the smallest of the two largest (i.e, the smallest of the first query), then you can probably do what you are doing. But you need an order by in you query, or there is no guarantee that last will really be other than an arbitrary result based on whatever comes last.

Usually when I need the min or max of something, I find it first, without other fields including. Then I use that result to pull back any other related information I need. The problem is that if the extra fields create additional grouping layers then you can't really "collapse the data down" to the one field you want the min or max for. Interestingly though, I have always used Min() or Max() and to be honest I'm not very familiar with results that use Last().

 

[squallleonhart]

Going with the two queries was the only way I could figure out how to get the second largest block absolutely for each group. I figured if I could isolate the two largest blocks the process would be easy from there. I may need to remove the extra fields for now and then create another query to get the rest of the data needed.