Multiple results returned when using SELECT TOP 1 my.Field

[slinfoot]

Hi

I'm using the following code to get the Mode(Most common result) from a set of values.

SELECT
       e.ID
     , e.ELR
     , e.Track_Id
     , e.DB_Start_Mileage
     , e.DB_End_Mileage
     , (
                SELECT
                         TOP 1 i.[Track Cat]
                FROM
                         qryINM2 AS i
                WHERE
                         (
                                  i.ELR            = e.ELR
                                  AND i.[Track Id] = e.Track_Id
                                  AND
                                  (
                                           (
                                                    StartMileage   <= e.DB_Start_Mileage
                                                    AND EndMileage >= e.DB_Start_Mileage
                                           )
                                           OR
                                           (
                                                    StartMileage   <= e.DB_End_Mileage
                                                    AND EndMileage >= e.DB_End_Mileage
                                           )
                                           OR
                                           (
                                                    StartMileage   >= e.DB_Start_Mileage
                                                    AND EndMileage <= e.DB_End_Mileage
                                           )
                                  )
                         )
                GROUP BY
                         i.[Track Cat]
                ORDER BY
                         Count(i.[Track Cat]) DESC
       )
FROM
       tblLNE_Eighths AS e

Unfortunately the TOP 1 bit returns more than 1 value if there are an equal amount of Top values and the query breaks as it can only return 1 from the sub query.
I know why this is happening but my question is what's the best way to just return the biggest value if the TOP 1 returns more that 1?
BTW I'm using TOP 1 as the field its looking at has string values (1A, 1, 2, 3, 4, 5, 6)

[June7]

Review http://allenbrowne.com/subquery-01.html#TopN

Need a unique identifier in the ORDER BY.

[slinfoot]

Thanks interesting article. I tried this but the difference between the articles example and mine is I’m grouping first to get the Mode value, so ordering by the PK(only unique value on the table)after I’ve ordered by Count of the group only works if I group by the PK too, which gives unintended results.
I’ve got it working in VBA using the query to populate a recordset then picking the first record. Just a bit slow that way.

[CJ_London]

not tested but how about SELECT DISTINCT TOP 1

[slinfoot]

Tried it. Can’t have two aggregate functions apparently

[CJ_London]

Ok - how about select max rather that top 1

[CJ_London]

the problem is you group by trackCat. Suggest create another query based on this query group on
e.ID
, e.ELR
, e.Track_Id
, e.DB_Start_Mileage
, e.DB_End_Mileage

and select first for the Track Cat value