rank() OVER (PARTITION BY f1 ORDER BY f2 DESC) 
ROW_NUMBER() () OVER (PARTITION BY f1 ORDER BY f2 DESC) 
For each matching f1 Field generates a line number , And in accordance with the f2 Sort Example
select DID,customerID,totalPrice,ROW_NUMBER() over(order by totalPrice) as rows from OP_Order

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAREAAAD8CAIAAADfWwcxAAAVtUlEQVR4nO2dPXLjOBOGcStVycmEuoMCre6gyImndAhHXF9Bob9yonTCqS3nPgq/QBQJEP9AN4Am+i3W1ixNAd0gHgKk8Iri9+/fv379GtGEWnj16gqIXEYFAq7bJmJm5vvvH/BNBhKj/LrVcUZVAsa+ynvFzDS9kcuImSHQghWr44yqBMzMEK4uYvt8fdm/fj3+IRadh4IZpcYAcwpslcZHxcw0Wd18IqE2uWcsJd+ue0+XNWfkCC/kT/Ixq+OzE3cxY0w8vmWYmSarK8SMvyJ0Zv6+n8Xxo8ApsCUe3zJdMvP5+rI/nvdCiOPHMiIfP/7++R6O0z+Wc/l+Frvr55+vt93juJe3W/QJ+/t+lgd904VWKv/9uhdCCLGeMxw/lOCXI6eQliN3108tTWPP8HVZU0Y3e3jKnz5O6lTHMc5ISU3FrlpMzy6TmTnx+JbplZmp3Zch+OttJ07v339v1/3u+vmARz7TUhPbO5nlhMkD/ftZ7K6Dzoxa/nL+tAjn4OV/7F+/5sjljijUPYaeIX0q7qJjD0+/PC9g6HcOtlzUFvvUsstl5llgfMv0yox9Yv1xEufhz8dpdx1eX07v38Px5e02X1wdg0xADzPtkXqJMmK4LsmmSYV6CyvMZIKMM2HhrdgwRm4r0HjAKjsL5zzOVGDmezi+vL2eH9f+/evH206+F/w4PedIwMyo5ScyowZmrAX2fsbFzDSndUUex0zQbQ/fzyAzY5ibPU72dL1fCFGmc7EXOX2mIV3DhqNQ5lG3614eIuyTn/U/1GdBygQP77mZY25myjGQGfPcTM0uixl+bpZ+yqVB39hew/EJEugzAKk0sd9pzwBuz+NnXNV7busFcq5FnamvDpamOKnfz9jDk/605Bg7NzO12Dq7BGaMice3TJfMoG3tfqfZTUa8DoBAC1asjjOqEjAzQ7g6zqhKwA0x8xtHc3pI5detroDIZVQg4CaYQdU4jthVVKyugMhlVCDg+sxgD9lCiML/5W3bWxPMIFXwKPy/gqrboBgilxF2wA0xA34xmAsXQhRjRghRsUExxMwULt+tQswUA+Y/HmcaEDPD40xlMTOFy3fLy0yWJylsnPn3H/HPv3DM+Br0fhGXe8ye++W5okM5Sj5mOUSIw/ATWLLtU2vZMvoZDkLR5S4VuqrYlEhoALFynAJLY4KVX0AOZp4GJghmLOPM/35PVUAyYx9n5j42ny7/np/hoP1T/5TORkhd+qfMCuoiP8Phch/vl2fnXxduSiQ0gFi5INfbFa78MuJxJnDPOI7jz3CQrsbSMeofQku2fmqtkC5yvxyGH6XIB0RGTYcFBxCrkIBzKu+CGef9DDAzvvuZZGZWl0a195unOD5mwiZG/i4yd8BlnPkZDrZin4kEBxCrgD7N40wH44zWB6Vj1J5q5iriU2t5u4g8pDxvGA6Xi/lCviQSHECsQgLOwbQLZqiPM6ZzHDl/i/jUWvFXgWmvXqats8JO09wBZwLjLR9bPM7491jOcSvMWD5rGDrc07UyzOQD4y6/gHic8e25XyyPRZdjpO65uri7SrZ/ai1nF1F6+1KmHra2JzyAWFkDtjYmUPlFxOOMZ4/8FYZ6r2z+DifiGYD1U2u5MtJq0L6fmY4wJhIYQKxsAdsbE6b8MuJ1AARUt4skCDvgLpgpBsx/vN6sATEzPM5UFjNTuHy3FGZA/aeTeJzJF7mMemEmw2fqEY8zmWJmCpfv1sIM9ibY2Mwb3NYEM0gVoBZuVN0GxRC5jLADbogZ8IsBNpBGkethXpHLiJlhZiqLXEbMDDNTWeQy6peZ5eVyzx/nx2EG2C0YX120txlvT2xGupM5xtvsOjhH3j6dub6tUWakF8L4X5OSyoxu9wVQTHUp3maDQXdZJ7k4IFOOic1IdzLbvc16AK6D8+Tp0z/DIW+FW6PMrPhxv5Rv2+PMLH3B/LRHXQatr8APPcaigC5iDW2RFkCgETpBzoB/hoPVDwdRPrpCmMEbZx6iwow+Giwjj9z51N4QcYxN/i5iDU3ZsQ4gyAidIlfA94u43DPtOq0z8/W2E6vXYnXJjN6t5j0OHqKOScvoMZuUw9X3jJYAvEboNNkDnhp2y8zkALMlZhzAjHYeYo9JzWgMGWicAx2w6cwW8FzvZpnJBGYzzLiBWR0+/zPlGItCuoj/hsZ1Q1XmNzSk37nZpudsOOb8GuB2mAkwCZsfSSUcY5Hjsu1/pmcPwGWEzpO3T29znFm+nBFCON+WvG1mdDuu2yQ8P/JNOyY2I92crO0x5VL7+5ltMgOy8ToAEJHLCDtgZgZY5HqYV+QyYmaYmcoil1FHzICamicxM/kil1EvzGSYlz1iZjJFLqNemOGNN0JbE8wgVcDjTL7IZYQdcEPMgF8M+H4GROQyYmaYmcoilxEzw8xUFrmM+mVmXj6TZp4JYSbQ3BslX4Nu29ssr8hRlqesF0gqbmjYpTNxZmzY8gvIuUZzWtSc/joNNzPh5t4o2Ru0A29zIACTLRPFWz7GBQxafhGFzM1u1/3u+ok7NwN8zxb4ODOLgLc5rB0fL3le/q/gOPNU1glvnpnP1xeccUZSmXHmoU17m6UZmLVLOow1QIoIGKl8THmY+Xrbod7PPATrRkdihoa3+e5/FbP2cxmlmTFarwHLx1bg3AzxNzSAf76Bvc3SLlPBoRPRHGHPLNpnBvG3msCBGdnbbN9j2VmFmawbmkaZKfGbgNCW2oeAmaHqbTb+Koaxo5ZjBupJaaPMfEvfz6QNMl5mws29UYJlZkveZgsddb6fYW9z1jMAWNVtUAyRywg7YGYGWOR6mFfkMmJmmJnKIpdRR8yAmponMTP5IpdRL8xkmJc9YmYyRS6jXpjhjTdCWxPMIFXA40y+yGWEHXBDzIBfDPh+BkTkMmJmmJnKIpcRM8PMVBa5jHpn5uOEtXZmxPldel+Dsrd5+QSGt9wd8DPIrPraZubz9QVtvRmU03Ule4Oyt1lqicsdyVs+xp0C2PJLyM3M7brfnU+JRoCYuRmgtZm9zbHeZn8Asep4nBmO4vSebJ6JYAb0MofGzFa9zf4AYtUtM9PPzWAzk+90XQmJme16m1uwysKWjysrMzMqJcaZ5n9DY8Pe5jassrDl48rGzO26Vx6yYL5PcxxB59PgzGzY24wBzNgrM4YBB5wZpOc2wMxs2NuM4y0fmRnUuRmI03UlWGY27G1G8pa7AzbEAV8+rngdAAGRywg7YGYGWOR6mFfkMmJmmJnKIpdRR8yAmponMTP5IpdRL8xkmJc9YmYyRS6jXpjhjTdCWxPMIFXA40y+yGWEHXBDzIBfDPh+BkTkMmJmmJnKIpcRM8PMVBa5jLpl5v28rKvAfZ+mcWlUsnzVbdvb7LeLK62NYS53L5BTlFhry8wkvkYzjpmf4VBosVMH3mavXVxp7WWRZtnL1jOSVFBbZebz9WV61zkqMz/D4XC51DXWbsfbvHLmaDmorQ0LiqSQPq37q2HLx5OTmecIiug5u1/E5V7bjL4hb7Pq01y3qtrawIZmSUEBZ5zyVpkZjmIaZ/DeQTt1TRLM0PA2L3co+ui9bu1p0Cl4PyPHnFNhq8xIG9I7aJWT1zYzFL3Nq5mX3tpS1fwbGhGqyMz6IQp7m20K6SK++Z6ptaVbipKXrfy6GmVGem/z+xn5WXPT4wxRb7O9cKm1FXhLjTMAZ7tRZr7x39s8q2Vm6HqbbU/plNYu+/2MKRzo8vHF6wAIiFxG2AEzM8Ai18O8IpcRM8PMVBa5jDpiBtTUPImZyRe5jHphJsO87BEzkylyGfXCDG+8EdqaYAapAh5n8kUuI+yAG2IG/GLA9zMgIpcRM8PMVBa5jJgZZqayyGXUMTPDcVpYMS08A2dGXj4CtnrG16Cb8jbP5ehrZ/QyDX8qvnYGpMJmmXk/i8k2k/y680D/DKzs1W3O2yyFvFr/aV7ovP5TcW+z13qdWX4R2ZlZPGdoczPYtZlPgY8zs5r0Nmt2ZVst+p+Ke5t91uvc8ssoxAuAyQz4zKw3b7NmV3a4QVd/quBtdluv88svIhczL6fjC+r9zN3/kuEEITHTpLfZZFeOYaa8t9luvYYpv4Cc4wz67wEsApymYTDTprfZaFeOG2fqeJvHMW9i2Cgz38Nxvu9H+j0AWS0z06q32WxXjrqfqeJtnoPf2v2M9JuAWOOM1GyQd6PAzDTsbXaUHfzcrKi3OcR6nVN+GdX9fsZgzc0XLDONe5sfkvnQvoQx5cLfz2SI1wEQELmMsANmZoBFrod5RS4jZoaZqSxyGXXEDKipeRIzky9yGfXCTIZ52SNmJlPkMuqFGd54I7Q1wQxSBTzO5ItcRtgBN8QM+MWA72dARC4jZoaZqSxyGTEzzExlkcuoU2a+3nbK0o6k99H6mUFYu+Fr0I17my0ZjepinadRVBHYOYg6BYGNEFM+rsLGmc/Xl6QlZz5mULy19uo68DYbMprltBPn2CY1RZwCx6LSlPJLKIiZjxPOO5twvLXg48wsAt5mR/zOOnLeoqwr4hQ4myW1fFwFMJPx0vNgLwCk0Jgh4G12xe9wkkP/MEP4KXA2S3L5uPIz8/W2SzPPhDCD4a1FYoaEt9kRv8NJDjovG8fumZmdZzjjDJHfA6DibbZnZP5gyMEJ6pqZZFdz4P0MhrcWnBlK3mZrRutPIrmaH+r5fuZ23Sfe/Qcxg+OtBWaGlrdZj186xuQkx0Am5hRs7bnZcMz8ibMNfD9DztusZWTIZTkYfl42jvz9DC4zCKrboBgilxF2wMwMsMj1MK/IZcTMMDOVRS6jjpgBNTVPYmbyRS6jXpjJMC97xMxkilxGvTDDG2+EtiaYQaqAx5l8kcsIO+CGmAG/GPD9DIjIZcTMMDOVRS4jZoaZqSxyGfXLzGxvTl6m6V/XrAhmFYevQbv1NhsWKuG8FsBn+MmuslVmPl9fJhfA+1ng+DRlwXk47NX17W2+a+9M1vcAyR4wjJu9YWae9syPE/q7AQG9teDjzCzS3ubV+n/dOgoIjTVgIDd7q8wsRoB021koM6AL0tGYIe5tvmvvTNb3AMk1G4dAs1lmHtMzIVInZuHMFLrITerV27zcSizjkr4HRA5mQNzsrTKzzM3SnWdhzBSbTNuq687brE+RYH8CyDXOQLjZG2VGdjWjvrcZ3CgIzszmvM3WSSaUHPczIAbRRpn5Ho5iGWfwngHAe2uBmdmet/lZr74HSt7J5DbHme+/fz5OzysY2nubMby1sMxs0tvM38/kiNcBEBC5jLADZmaARa6HeUUuI2aGmakschl1xAyoqXkSM5Mvchn1wkyGedkjZiZT5DLqhRneeCO0NcEMUgU8zuSLXEbYATfEDPjFgO9nQEQuI2aGmakschkxM8xMZZHLqF9m5rUzSO85G6VlhgUW1T61HW+z0RzuKFNv7YTf5A8R9hlvlZlljSaa50xZJFjAp7lRb/Mc+OXuKlNv7aR3v4QI+4w3yszKC4Cyrlk6U4BrNcHHmVlNepvniNcFrMvUW9sZUo6wz3ijzKy8ADj+mbkJIU8ZGjNNepstJVqiVVrbGVKWsM94q8ws9zO78wnNczZNhYpMDCZtydu8lKAPXIYy1dauwQzMGW+XGRkeFGZw5tMYzLTpbQ6PVq11uaEqzQzQGW+fGSyfJtI5A2emVW/zEkzIqGVo7eL3M1BnvFFmvt52kz1zOAqk52bqbwU1+gygdW+z2vccZeqtXf65GdAZb5SZ7yLfz2B8PwDLTOveZjV2U5mmXCK/IIoV9hlvl5n8jdcBgIhcRtgBMzPAItfDvCKXETPDzFQWuYw6YgbU1DyJmckXuYx6YSbDvOwRM5Mpchn1wgxvvBHammAGqQIeZ/JFLiPsgBtiBvxiwPczICKXETPDzFQWuYyYGWamsshl1Dkzikkz9k0B4WtnShieljo34m0erVZh45JLeWHNdHjxtTOGGEDLLyEHM7fr/pHbk5n4N58Fr9Es4znbnLfZYBXWM5qlNXL5NZpAJ7pZZrRxZjjOw0vgm5yjvACNrmuW1Jy32WoVNsWv11HV24xSfhFFMJPwtsAoL0Cz/pmnGvQ226zCFmbUWVEdz1n+zKxrZpbpLeR7g5GYadTbbLYKm5jRvCsVmNm8f6YAM7MAf6wJg5lGvc3WGxLPVOtxdJ3fA1BjwCsfTzXvZyQ17NNs2Nts7/RBzFS4n9FjQCsfTzHMQD83U576NPtbTS17m61THcvccvFrruaLhZ6bGWIALb+M+PsZz57Gvc2WL1jkjAy5zHVV9DZv+BlA1sbrAEBELiPsgJkZYJHrYV6Ry4iZYWYqi1xGHTEDamqexMzki1xGvTCTYV72iJnJFLmMemGGN94IbU0wg1QBjzP5IpcRdsANMQN+MeD7GRCRy4iZYWYqi1xGzAwzU1nkMuqcGf0FtBGvpA1jBnZ9IKK3Wbck+0zCqzUsgZ/KykhzO3vtxB4jd4IgTkFO+bhyMKN5mw17MplxGHHTFVNdwB6DATvEJKyXnG4tjsjI4HZ2dEfTx3HfQxZyCnLKLyEeZwL3jEs3ijAJS/+XYS2OyEh3O/s7vzmOnLPS7TjDzKiaL+ERJmG1N4d+Kicjze3stxMvH4dyoTEzzMzU8TTrh9ckrF63U63FcRmt3M5+OzEzEydmJnDPaOxzPpOwObVYa3HS3MxQpKUWZiZOzEzgnnFM+a0mZzkI9zPuTu9lhu9nQsTMePZIg8sypwo2CRvLibYWR2RkmgH67MShw1S4mJmumRmdlmCfSTi0HMDvZ/QytXpX+fL3M3HidQAERC4j7ICZGWCR62FekcuImWFmKotcRh0xA2pqnsTM5ItcRr0wk2Fe9oiZyRS5jHphhjfeCG1NMINUAY8z+SKXEXbADTEDfjHg+xkQkcuImWFmKotcRswMM1NZ5DLqnJlC7wUAXD3ja9C+vM2SfWZdk/FPWrIpigo4oQ80y8zayfz1tnuicrvuYd5Ba1jBmC17g/bpbVYPd9icL3dTsikKDzitDzTLjGGcmTfwdwMCvjQIfJx5iqa3WTnO2sbanwqva46rkiIzMOOMpKbfDTiJqLdZjt9Wh9FqgzLOPBRq6UstH1cpzHy97cT0kkAYZn4A33TO3uaVt1na7Z6XrQoodj+zVBrcB4gxEw5MIDOwwIzsbfa531wfV2Is59OM7QOUmIkCJoQZcGBG9jabOIyal3k/4lXUKUjoA3SYGY7hvwYYxMwd8nXNs2CZoe5tflYehAzUk8y4gOPrIcPM8uWMEEKI/Odm9+AXF0cJfJyh7m3W8nMNcHqQCQoPOK0PNM5M1sbrAEBELiPsgJkZYJHrYV6Ry4iZYWYqi1xGHTEDamqexMzki1xGvTCTYV72iJnJFLmMemGGN96obBWBWZipGwSLRUjMDIsVJ2aGxYoTM8NixYmZYbHi9H/d6P50hiqisAAAAABJRU5ErkJggg==" alt="" />

 Users can talk about horizontal comparison after sorting multi column data , for example ,oe An inquiry for , After making an inquiry to multiple suppliers , Need to take before 3 The price of each supplier is shown in a horizontal column .
select
product_id,
product_qty,
max(part1) as parter1,
max(price1) as price1,
max(part2) as parter2,
max(price2) as price2,
max(part3) as parter3,
max(price3) as price3
from(
select
product_id, product_qty,
case when abc=1 then partner_id end as part1,
case when abc=2 then partner_id end as part2,
case when abc=3 then partner_id end as part3,
case when abc=1 then price_unit end as price1,
case when abc=2 then price_unit end as price2,
case when abc=3 then price_unit end as price3
from (
select
pol.id, pol.product_id, pol.partner_id, product_qty , pol.price_unit,
rank() OVER (PARTITION BY pol.product_id order by pol.partner_id) as abc
from
purchase_requisition as rq
left join purchase_order as po on (po.requisition_id = rq.id)
left join purchase_order_line as pol on (pol.order_id = po.id)
where rq.id = 44
) as t
) as tt group by product_id,product_qty

give the result as follows :

psql rank row More articles about

  1. 【 linear algebra 】3-3: Rank (Rank)

    title: [ linear algebra ]3-3: Rank (Rank) categories: Mathematic Linear Algebra keywords: Rank Row Reduced form Pivot ...

  2. 【 original 】 Open source .NET Arrange and assemble components KwCombinatorics Use ( 3、 ... and )—— Cartesian product combination

            The total catalog of all articles in this blog : Blog contents - Real time updates The rest of this blog .NET Open source project article directory :[ Catalog ] The rest of this blog .NET Open source project article directory KwCombinatorics Component article directory : 1. ...

  3. 【 original 】 Open source .NET Arrange and assemble components KwCombinatorics Use ( Two )—— Permutation generates

            The total catalog of all articles in this blog : Blog contents - Real time updates The rest of this blog .NET Open source project article directory :[ Catalog ] The rest of this blog .NET Open source project article directory KwCombinatorics Component article directory : 1. ...

  4. 【 original 】 Open source .NET Arrange and assemble components KwCombinatorics Use ( One )— Combinatorial generation

           The total catalog of all articles in this blog : Blog contents - Real time updates The rest of this blog .NET Open source project article directory :[ Catalog ] The rest of this blog .NET Open source project article directory KwCombinatorics Component article directory : 1. ...

  5. Python Batch processing CSV file

    #encoding: utf-8 __author__ = 'DELL' import csv import glob import datetime import sys import os rel ...

  6. Sqlite Quickly insert data into local tables

    Original use Insert The method is too slow , I found... On the Internet https://www.cnblogs.com/yisen-code/p/6897524.html The train of thought is : Open transaction , Start pretreatment , And then put SQL Pass in specific values with parameters to ...

  7. Python The toolkit [1] -> pandas Data preprocessing -> pandas Library and use summary

    pandas Data preprocessing / pandas data pre-processing Catalog About pandas pandas library pandas Basic operation pandas Calculation pandas Of Series ...

  8. Step by step towards the international garbled competition -- A spoof C Language

    Everyone has been emphasizing standard coding . But there is a competition in the world for masters to entertain -- International garbled competition . What can be written is a deep understanding of the language master. I never thought I could " A spoof "C, It's always been honest coding . Just a few days ago ...

  9. PostgreSQL Replication Chapter five Set up synchronous replication (1)

    up to now , We've dealt with file based replication ( Or log shipping ) And simple stream based replication settings . In two cases , stay master After the last transaction is committed , The data is submitted , from slave receive . stay master Submit and slave It's actually completely grounded ...

Random recommendation

  1. Effective Java 40 Design method signatures carefully

    Principle Choose method names carefully. Don't go overboard in providing convenience methods. Avoid ...

  2. JQuery Traverse the specified id Of div name There are several ways to calculate the value

    JQuery Traverse the specified id Of div name There are several ways to calculate the value : Method 1 $("#div1 :text").each(function () { var this_id = $(this). ...

  3. JS in Date.parse Method returns NaN Solution

    Date.parse() Method : // Convert the time string to seconds var date_str = '2013-12-3 18:32:00'; alert(date_str.replace(/-/g,'/')) ...

  4. awk The use of multiple arrays

    #!/bin/bash awk 'BEGIN{printf "%-60s %-10s %-10s %-10s\n","url","count" ...

  5. iOS Open source library -- The most complete arrangement

    youtube Download artifact :https://github.com/rg3/youtube-dl I'll wipe it vim plug-in unit :https://github.com/Valloric/YouCompleteMevim insert ...

  6. MySQL Query duplicate data , And partial and complete field de duplication

    1. Look up redundant duplicate records in the table ( Multiple fields ) select * from vitae a where (a.peopleId,a.seq) in  (select peopleId,seq from vit ...

  7. Maven Remove the project version number when packing

    Maven After packing ,jar or war The file name contains the version number information , Such as projectname0.0.1-SNAPSHOT.jar etc. , How to get rid of it ? terms of settlement : Open the project pom.xml file , stay <build> ...

  8. 1.9 Distributed coordination services -Zookeeper( One )

    Preface The characteristics of distributed environment Distribution concurrency During the operation of the program , Concurrent operations are common . For example, multiple nodes in the same distributed system , Access a shared resource at the same time . database . Distributed storage Disorder Message communication between processes , There will be inconsistent order ...

  9. Android 5.0 edition USB Debug mode open method

    Android 4.2 edition USB Debug mode open method 1. Get into “ Set up ” page , Click on “ About tablets ”. See the red box below .   2. Crazy Click “ Version number ”, See the red box below , Until it appears “ You are now in developer mode !” ...

  10. use windows The performance monitor detects sqlserver Common indicators

    Reprinted address :https://www.cnblogs.com/xdong/p/4296072.html