How to select records in the order they were inserted, and then group them by a type

I have a query I can not seem to get to work. I have a table like this:

ThingID, FK_ThingTypeID, Etc…

And I want to select records in the order they were inserted, but group them by type. So if the data looks in the table is in the following order:

ThingID, FK_ThingTypeID
1        1
2        2
3        1
4        1

I want to get select the records like this:

ThingID, FK_ThingTypeID
1        1
3        1
4        1
2        2

So they are in the order they were added, but grouped by type.

I have tried using ORDER BY and GROUP BY for this but no combination of what I try works, and all the GROUP BY examples I see are working with aggregate functions. I am not interested in counts or max etc.. I just want to order the records as above. I have tried just using an ORDER BY ThingID, FK_ThingTypeID, but this just lists them by ID and does not group by the types. There are a stack more columns in the table and using GROUP BY requires that I add all these, and then it doesn’t work anyway.

Can anyone give an example of an approach to achieve the result I am looking for?

Thanks for your time.

Answer

Thanks for those that responded. Here was the solution:

SELECT
    t.*
FROM
    Thing t
ORDER BY 
    (
        SELECT 
            min(ref.ThingID) 
        FROM Thing ref 
        WHERE
            ref.ThingTypeID = t.ThingTypeID 
    ),
    t.ThingID

Advertisement

Answer