I have a queue table with the following sample data:
id company location
1 acme new york
2 acme philadelphia
3 genco st.louis
4 genco san diego
5 genco san francisco
6 acme miami
I want to query for the queue position of each company group to show where in line each company is (imagine acme has 1,000 rows before 500 Genco starts and then acme has 5,000 more after the Genco records process). Result I would want would look like the following:
queuePositionId company
1 acme
3 genco
6 acme
I played around with ranking and grouping but the fact that a company group can be multiple times anywhere in the queue kept messing with my aggregates. I also tried a dense_rank
but couldn't figure out the ordering. Any ideas?
dense_rank()
row_number()
method see my explanation here