I have a table like this:
| Type | rank |
|---|---|
| A | 1 |
| A | 2 |
| B | 3 |
| A | 4 |
| B | 5 |
| B | 6 |
| B | 7 |
| A | 8 |
And i want convert it to this table with sql query:
| Type | rank |
|---|---|
| A | 2 |
| B | 3 |
| A | 4 |
| B | 7 |
| A | 8 |
How can I do this with and with out window functions?
CodePudding user response：
MySQL can do it:
SELECT `type`,`rank`
FROM (
SELECT
`type`,
lag(`type`) over (order by `rank` desc) as L,
case when `type`=lag(`type`) over (order by `rank` desc) then 1 else 0 end C,
`rank`
FROM table1
ORDER BY `rank`
) x WHERE c=0
Sorry, I have no knowledge of openquery, and I am too lazy right now to search for it.....