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.
Advertisement
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