Select first complete row from each group
Select first complete row from each group
(OP)
Hi,
I'm trying to create this query in Informix that will return the first row from each of these "Appt"'s. I originally wanted to find the minimum date but unfortunately, there are multiple PO's and Appt's that have the same date. I know that this should probably be simple but I can't seem to get it. Help!
Mark
Appt PO Date
1 1050422199 11/2/2017
1 4050407179 11/2/2017
1 4050407187 11/2/2017
1 1050422172 11/3/2017
1 1050422185 11/4/2017
2 4150393598 11/2/2017
3 4050407178 11/2/2017
Return:
Appt PO Date
1 1050422199 11/2/2017
2 4150393598 11/2/2017
3 4050407178 11/2/2017
I'm trying to create this query in Informix that will return the first row from each of these "Appt"'s. I originally wanted to find the minimum date but unfortunately, there are multiple PO's and Appt's that have the same date. I know that this should probably be simple but I can't seem to get it. Help!
Mark
Appt PO Date
1 1050422199 11/2/2017
1 4050407179 11/2/2017
1 4050407187 11/2/2017
1 1050422172 11/3/2017
1 1050422185 11/4/2017
2 4150393598 11/2/2017
3 4050407178 11/2/2017
Return:
Appt PO Date
1 1050422199 11/2/2017
2 4150393598 11/2/2017
3 4050407178 11/2/2017
RE: Select first complete row from each group
ROW_NUMBER() over (partition by appt order by Date asc)
Wrap another query around this and choose where row=1
Mark