Sort a subset within a query

[mitchbvi]

I am trying to figure out how to sort a subset in a query. I have three fields BatchID,Month and Year. The BatchID can have multiple months and years. I would like to order the final sort so as the Batchs appeared in the order of the highest date combination with in each Batch. Hopefully the following table explains this.


The following is the SQL of my first Query which establish's the lowest and highest date in each batch.

SELECT tblRevOnlySelYear.BatchID, tblRevOnlySelYear.OperatorID, Max([Year] & IIf(Len([Month])=1,0 & [Month],[Month])) AS Expr1FROM tblRevOnlySelYear
GROUP BY tblRevOnlySelYear.BatchID, tblRevOnlySelYear.OperatorID
HAVING (((tblRevOnlySelYear.OperatorID)=1))
ORDER BY Max([Year] & IIf(Len([Month])=1,0 & [Month],[Month]));

The next SQL is the second query which results in the Batchs in the correct order by year but the months are not correct.

SELECT tblRevOnlySelYear.BatchID, tblRevOnlySelYear.OperatorID, tblRevOnlySelYear.Month, tblRevOnlySelYear.Year, qryRevTest4_1.[1stDate], qryRevTest4_1.LastDateFROM qryRevTest4 AS qryRevTest4_1 INNER JOIN tblRevOnlySelYear ON qryRevTest4_1.BatchID = tblRevOnlySelYear.BatchID
WHERE (((tblRevOnlySelYear.OperatorID)=1))
ORDER BY qryRevTest4_1.LastDate;

Sorry about the size of the files.

[ranman256]

I hope your MONTH & YEAR are numeric, otherwise the sorts wont work, But you made LASTDATE field which will,
so the sort is:
order by BATCHID ,LASTDATE

BUT
if you only want 1 date (the max) for each batch,
select BATCHID, max(LASTDATE) as MaxDate from table

[John_G]

It's not clear what you want to sort by, but the ORDER BY can contain as many fields as you need for the sort you require - they don't have to be combined into one expression. You can have some on ascending order (the default), and some on descending order.

So, if you wanted the batches in ascending order, and the dates in descending order within each batch, you could use:

ORDER BY BatchID, Year DESC, Month DESC for the first table, and

ORDER BY BatchID, LastDate DESC for the second one.

Just remember that the order you specify the fields in the ORDER BY is important.

John

[mitchbvi]

Thanks for taking the time to respond unfortunately the one thing I do not want to do is sort by BatchID, I guess I did not explain my self very well. What I need is to sort with in the BatchID month and year in ascending order. Then I want to sort the whole batchID groups by the highest date . In the result that I showed in my original post you will see that the BatchID's are not sequential and are in order as far as the year is concerned. However they are not sorted in order for the month, the first two batches in the example are correct but batch 4887 is not with month 3 appearing before month 2.

Peter

[mitchbvi]

Hi John

Thank you for taking the time to reply. I have replied to ranman post and I hope it now explains what I am trying to achieve. In short form I want each batch sorted by year and month in ascending order and I then want each batch sorted in the highest date in each batch. My example does that for the year but not for the month.

Thanks again the help is greatly appreciated.

Peter

[ranman256]

You keep saying sort by Batch , yet you DONT want to sort by batchID.
??!!

[John_G]

Try ORDER BY lastdate, batchID, Month, Year

This assumes that every record for ony one BatchID has the same LastDate.

You are going to have to sort by BatchID somewhere, otherwise there is no guarantee that all records for the same batch will be kept together.

John

[mitchbvi]

My Apologies for the lack of clarity.
First I need to sort within each Batch by year and month in ascending order for both month and year.
Then I need each batch sorted not by the batchID but by the highest date within each batch, hope that makes it clearer.

Thanks again

Peter

[mitchbvi]

Yes I need two queries one that establishes what the last date is for each batch and that was the first SQL in the original post . that is the last sort order so the object is to keep the records with in each batch together and you have given me the clue I needed. I have now concatenated ( lastdate ,batchid,year,month) and that looks Ok just need to test it on the full DB.

Cannot believe something so simple has taken me so long, started to write code to sort recordsets made up of each batch and got nowhere with that, talk about Occam's Razor.

Thanks again

Peter