Sort a Query but a unselected Field

[Maldrid]

I have a query that selects two fields and they must be Distinct, but I want the recordset to come sorted by a field I do not select. Is there a way of accomplishing this doing a sub select? Or some other fashion?


Here is my query that I have right now

sSQLQuery = "SELECT DISTINCT [Bill Code], [Price Type], [Reference Code] FROM CBill" & sCBILL & " WHERE [KitchenID] = '" & sKitchenID_P _
                    & "' AND [Customer ID] = '" & sCustomerID_P _
                    & "' AND [Flight #] = '" & rsFlight("Flight #") _
                    & "' AND [Flight # Seq] = '" & rsFlight("Flight # Seq") _
                    & "' AND [Segment #] = '" & rsFlight("Segment #") _
                    & "' AND [Flight Date] BETWEEN #" & sFromDate_P & "# AND #" & sToDate_P _
                    & "# AND [Recap Type] = '" & sRecapType_P _
                    & "' AND [Account Code] = '" & rsAccount("Account Code") & "' ORDER BY [SEQ #]"

Now obviously this query will not work because I am selecting Bill Code and Price Type and trying to sort by SEQ #, but is there a way of doing this?

[brucevde]

What database? Your syntax is valid in SQL Server.

Try a subquery (not all databases support this syntax).

sSQLQuery = "Select [Bill Code], [Price Type], [Reference Code]  From (SELECT DISTINCT [SEQ #], [Bill Code], [Price Type], [Reference Code] FROM CBill" & sCBILL & " WHERE [KitchenID] = '" & sKitchenID_P _
                    & "' AND [Customer ID] = '" & sCustomerID_P _
                    & "' AND [Flight #] = '" & rsFlight("Flight #") _
                    & "' AND [Flight # Seq] = '" & rsFlight("Flight # Seq") _
                    & "' AND [Segment #] = '" & rsFlight("Segment #") _
                    & "' AND [Flight Date] BETWEEN #" & sFromDate_P & "# AND #" & sToDate_P _
                    & "# AND [Recap Type] = '" & sRecapType_P _
                    & "' AND [Account Code] = '" & rsAccount("Account Code") & "' ORDER BY [SEQ #])"

[Maldrid]

This is for an Access Database.

Question though. Won't I need Distinct on the first selection and not the subquery? The subquery will grab all records anyway because SEQ # is the primary key and that is distinct.

[Maldrid]

Hmm that don't seem to work. I tried it and the query works but for some reason it unsorts it after I sorted it in the subquery.

[Maldrid]

Ok I figured how to do this. I needed to use group by.

sSQLQuery = "Select [Bill Code], [Price Type], [Reference Code], MAX([SEQ #]) AS SEQ From CBill" & sCBILL & " WHERE [KitchenID] = '" & sKitchenID_P _
                    & "' AND [Customer ID] = '" & sCustomerID_P _
                    & "' AND [Flight #] = '" & rsFlight("Flight #") _
                    & "' AND [Flight # Seq] = '" & rsFlight("Flight # Seq") _
                    & "' AND [Segment #] = '" & rsFlight("Segment #") _
                    & "' AND [Flight Date] BETWEEN #" & sFromDate_P & "# AND #" & sToDate_P _
                    & "# AND [Recap Type] = '" & sRecapType_P _
                    & "' AND [Account Code] = '" & rsAccount("Account Code") & "' GROUP BY [Bill Code], [Price Type], [Reference Code] ORDER BY MAX([SEQ #])"

[Maldrid]

Oh and thanks for replying to my post Bruce!