I have a table where the ID is repeated, but other info is different. I wish to select only one record for each ID in the table, and have some criteria so the correct ones are picked.
In my attempt to solve it, I first created an ordering query, so that for each ID, the one to pick was at the top of the list. Then I tried a group by query on this subquery, using the first() function to get the top ones.
Many of you will be ahead of me already - this doesn't work! (it picks the IDs as if there weren't sorted)
But, from what I could find out, when working on a sorted query, the first() function should work as I want it to! So where's the problem happening??
Sorry but I really need to be talked through this! Where would I use the select distinct? How would this work with picking my preferred row? (i.e, the one nearer the top of the list when sorted by ID and the second value)
More explanation - my table looks like this:
ID Company StillWorksHere
111 X No
112 Y Yes
112 X No
112 Z No
113 Y Yes
113 Z No
As you can see, I wanna pick the top one for each ID (either the one they currently work for, otherwise it doesn't matter)