# 'How do I return single orderid having same rank?

I have the following output:

Orderid | Time | State | Order_rank |
---|---|---|---|

1 | 10.15 | mfr | 1 |

1 | 10.15 | delivered | 1 |

2 | 12.10 | picked | 1 |

2 | 12.10 | mfr | 1 |

Here I have ranked the order ids wrt to the latest time and put the condition in where clause "where order_rank = 1", but I want to return a single entry for one orderjobid, i.e. for orderid 1 I want to return the delivered state entry and for orderid 2 I want mfr state entry etc.

This is just a sample data, I have more than 1000s of orderids, how do I return single entry for the same rank?

## Solution 1:^{[1]}

Assuming you code looks something like this:

```
SELECT * FROM (
SELECT
order_id,
time,
state,
RANK() over (partition by order_id order by time) as order_rank
FROM VALUES
(1,'10.15','mfr'),
(1,'10.15','delivered'),
(2,'12.10','picked'),
(2,'12.10','mfr')
v(order_id, time, state)
)
WHERE order_rank = 1;
```

which gives the output you have.

Not the solution you are looking for, but you can move from the RANK and sub-select and WHERE to use QUALIFY and hide the order_rank line:

```
SELECT
order_id,
time,
state
FROM VALUES
(1,'10.15','mfr'),
(1,'10.15','delivered'),
(2,'12.10','picked'),
(2,'12.10','mfr')
v(order_id, time, state)
QUALIFY RANK() over (partition by order_id order by time) = 1;
```

But as userMT notes, you want to put a numeric ranking into the selection

like:

```
case state
when 'delivered' then 1
when 'mfr' then 2
when 'picked' then 3
else 4
end as state_rank,
```

so if you have it as a separate field, it's easier to read:

```
SELECT
order_id,
time,
state,
case state
when 'delivered' then 1
when 'mfr' then 2
when 'picked' then 3
else 4
end as state_rank
FROM VALUES
(1,'10.15','mfr'),
(1,'10.15','delivered'),
(2,'12.10','picked'),
(2,'12.10','mfr')
v(order_id, time, state)
QUALIFY RANK() over (partition by order_id order by time,state_rank) = 1;
```

giving:

ORDER_ID | TIME | STATE | STATE_RANK |
---|---|---|---|

1 | 10.15 | delivered | 1 |

2 | 12.10 | mfr | 2 |

but you can mash the case into the rank also:

```
SELECT
order_id,
time,
state
FROM VALUES
(1,'10.15','mfr'),
(1,'10.15','delivered'),
(2,'12.10','picked'),
(2,'12.10','mfr')
v(order_id, time, state)
QUALIFY RANK() over (partition by order_id order by time, case state when 'delivered' then 1 when 'mfr' then 2 when 'picked' then 3 else 4 end) = 1;
```

gives:

ORDER_ID | TIME | STATE |
---|---|---|

1 | 10.15 | delivered |

2 | 12.10 | mfr |

## Solution 2:^{[2]}

you could try
`row_number() over (partition by Orderid order by State) `

and select the first one

## Solution 3:^{[3]}

Extend your ranking to include not only the time, but also an expression of state, like.

```
Order time desc,
case state when 'picked' then 1
When 'mfr' then 2
When 'delivered' then 3
...
End asc
```

This way we assign a sort order 1,2,3 to each state.

Edited to make it correct as per comments from @simeon-pilgrim (thanks)

## Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution | Source |
---|---|

Solution 1 | Simeon Pilgrim |

Solution 2 | flmelody |

Solution 3 |