仅从SQL查询中获得第一行 [英] Get only top row from SQL Query

问题描述

您好，我在使用以下查询获取前1行时遇到了麻烦. 在Oracle plsql中.

Hello I'm having trouble with getting the Top 1 row using the below query. its in oracle, plsql.

SELECT per.person_no, 
       perbus.person_no, 
       perbus.edit_dt, 
       perbus.org_name, 
       perbus.FIRST_NAME 
FROM users.persons per
    LEFT OUTER JOIN (
        SELECT ASSOC.edit_dt,PER_CONTACTS.PERSON_NO, 
               PER_CONTACTS.PERSON_ID AS PER_ID,
               PER.EXTERNAL_PERSON_ID AS EXT_PER_ID,
               PER_CONTACTS.LNAME||', '||PER_CONTACTS.FNAME AS NAME, 
               PER_CONTACTS.FNAME AS FIRST_NAME, 
               PER_CONTACTS.MNAME AS MIDDLE_NAME, 
               PER_CONTACTS.LNAME AS LAST_NAME,
               ORG.ORG_NAME AS ORG_NAME,
               T_ORG.ORG_ID AS ORG_ID,
               T_ORG.ORG_DISPLAY_ID AS EXT_ORG_ID,
               rownum AS rn
        FROM USERS.PER_CONTACTS PER_CONTACTS
            LEFT OUTER JOIN USERS.PERSONS PER ON PER.PERSON_ID = PER_CONTACTS.PERSON_ID
            LEFT OUTER JOIN USERS.ASSOC ASSOC ON ASSOC.PERSON_ID = PER.PERSON_ID
            LEFT OUTER JOIN USERS.T_ORG T_ORG ON T_ORG.T_ORG_ID = ASSOC.T_ORG_ID
            LEFT OUTER JOIN USERS.ORG ORG ON ORG.ORG_ID = T_ORG.ORG_ID
        WHERE PER_CONTACTS.CONTACT_ROLE_LOV = 'EMPLOYEE' AND PER_CONTACTS.PERSON_NO IN (SELECT PERSON_NO FROM USERS.QA_APPROVED)
        ORDER BY ASSOC.edit_dt DESC
    ) 
    perbus ON perbus.PERSON_NO = PER.PERSON_NO 
WHERE PER.PERSON_NO IN (SELECT PERSON_NO FROM USERS.QA_APPROVED)

我是oracle的新手，在上述查询中，由于数据的缘故，我得到了重复数据-我需要根据 Assoc.edit_dt 获得第一条记录.

I'm new to oracle and in the above query i'm getting duplicates because of the data - and i need to get the first record based on the Assoc.edit_dt.

我可以按Assoc.edit_dt排序记录，但是当我在Join 中使用诸如 row_num = 1 的子句时，我的输出查询为null所有perbus表字段.

I'm able to order the record by Assoc.edit_dt, but when i use a clause like where row_num = 1 inside the Join, my output query is null for all perbus table fields.

请告知.

推荐答案

您可以使用结果偏移量并获取第一个子句(仅适用于12c)

You can use Result offset and fetch first clauses (only works on 12c)

只需添加

FETCH FIRST ROW ONLY;

在order by子句之后

after the order by clause

有关更多信息. 阅读此

希望这对您有帮助=)

SELECT per.person_no, 
       perbus.person_no, 
       perbus.edit_dt, 
       perbus.org_name, 
       perbus.FIRST_NAME 
FROM users.persons per
    LEFT OUTER JOIN (
        SELECT ASSOC.edit_dt AS EDIT_DT,PER_CONTACTS.PERSON_NO, 
               PER_CONTACTS.PERSON_ID AS PER_ID,
               PER.EXTERNAL_PERSON_ID AS EXT_PER_ID,
               PER_CONTACTS.LNAME||', '||PER_CONTACTS.FNAME AS NAME, 
               PER_CONTACTS.FNAME AS FIRST_NAME, 
               PER_CONTACTS.MNAME AS MIDDLE_NAME, 
               PER_CONTACTS.LNAME AS LAST_NAME,
               ORG.ORG_NAME AS ORG_NAME,
               T_ORG.ORG_ID AS ORG_ID,
               T_ORG.ORG_DISPLAY_ID AS EXT_ORG_ID,
               rownum AS rn
        FROM USERS.PER_CONTACTS PER_CONTACTS
            LEFT OUTER JOIN USERS.PERSONS PER ON PER.PERSON_ID = PER_CONTACTS.PERSON_ID
            LEFT OUTER JOIN USERS.ASSOC ASSOC ON ASSOC.PERSON_ID = PER.PERSON_ID
            LEFT OUTER JOIN USERS.T_ORG T_ORG ON T_ORG.T_ORG_ID = ASSOC.T_ORG_ID
            LEFT OUTER JOIN USERS.ORG ORG ON ORG.ORG_ID = T_ORG.ORG_ID
        WHERE PER_CONTACTS.CONTACT_ROLE_LOV = 'EMPLOYEE' AND PER_CONTACTS.PERSON_NO IN (SELECT PERSON_NO FROM USERS.QA_APPROVED)
        --ORDER BY ASSOC.edit_dt DESC
    ) 
    perbus ON perbus.PERSON_NO = PER.PERSON_NO 
WHERE PER.PERSON_NO IN (SELECT PERSON_NO FROM USERS.QA_APPROVED)
ORDER BY perbus.edit_dt DESC 
FETCH FIRST ROW ONLY;

仅供参考，您为所有列提供了别名，除了一个，即 assoc.edit_dt ，但您在主查询中将其称为具有别名.为 assoc.edit_dt

FYI, you gave all the columns their aliases except for one and that is assoc.edit_dt but you are calling it in the main query as if it has an alias.So i took the previlige to add the alias for assoc.edit_dt

这篇关于仅从SQL查询中获得第一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！