SELECT pastki so'rovida ORACLE ORDER BY

DARETO-ning tarixiy maydoniga buyurtma qilingan subquery-da BARCHER maydonini SUBJECT-ni tanlashni xohlayman. Aniq: eng yangi yozuvning ONE mavzu maydoni, shuning uchun sana maydoniga buyurtma qilinadi.

SELECT MDKAMVP.MDKAMDA_SID, 
(select SUBJECT from mdkndlst where rownum=1 and SUBJECT is not null 
order by DATETO) NAKTION, MDKAMVP.ROWID ROW_ID 
FROM SPRINGV2.MDKAMVP order by MDKAMVP.INDSTATUS

Xato:

ORA-00907: Rechte Klammer fehlt
00907. 00000 -  "missing right parenthesis"
*Cause:    
*Action:
Fehler in Zeile: 8 Spalte: 99

It works without the order by DATETO

1
@jarlh bu mdkndlst jadvalining eng so'nggi kiritilishini olishni istaydi
qo'shib qo'ydi muallif florian.isopp, manba
Ko'rib turganingizdek, Oracle pastki so'rovda tartibini buyrug'idan foydalanishga ruxsat bermaydi. Jadvalning tuzilmalarini, ayrim namunaviy ma'lumotlarni va ushbu namunaga erishmoqchi bo'lgan natijalarni almashasizmi?
qo'shib qo'ydi muallif Mureinik, manba
Bu ORDER BY hech qanday mantiqqa o'rin bermaydi ... Siz u bilan nima hal qilishga harakat qilyapsiz?
qo'shib qo'ydi muallif jarlh, manba
Kechirasiz, rowno farq qilmadim. (Men Oracle yigit emasman, shuning uchun Ilk bor/LIMIT/TOP va boshqalar.
qo'shib qo'ydi muallif jarlh, manba

10 javoblar

Oracle 12c ilovasida yangi FETCH FIRST so'zidan foydalanishingiz mumkin:

SELECT
    mdkamvp.mdkamda_sid,
    (SELECT subject
        FROM mdkndlst
        WHERE subject IS NOT NULL
        ORDER BY dateto
        FETCH FIRST 1 ROW ONLY) naktion,
    mdkamvp.rowid row_id
FROM springv2.mdkamvp
ORDER BY mdkamvp.indstatus
1
qo'shib qo'ydi

Oracle 12c ilovasida yangi FETCH FIRST so'zidan foydalanishingiz mumkin:

SELECT
    mdkamvp.mdkamda_sid,
    (SELECT subject
        FROM mdkndlst
        WHERE subject IS NOT NULL
        ORDER BY dateto
        FETCH FIRST 1 ROW ONLY) naktion,
    mdkamvp.rowid row_id
FROM springv2.mdkamvp
ORDER BY mdkamvp.indstatus
1
qo'shib qo'ydi

Oracle 12c ilovasida yangi FETCH FIRST so'zidan foydalanishingiz mumkin:

SELECT
    mdkamvp.mdkamda_sid,
    (SELECT subject
        FROM mdkndlst
        WHERE subject IS NOT NULL
        ORDER BY dateto
        FETCH FIRST 1 ROW ONLY) naktion,
    mdkamvp.rowid row_id
FROM springv2.mdkamvp
ORDER BY mdkamvp.indstatus
1
qo'shib qo'ydi

rownum satrini belgilash ORDER BY so'zini qo'llashdan oldin amalga oshiriladi, shuning uchun bu so'rov

SELECT subject
FROM   mdkndlst
WHERE  rownum = 1
       AND subject IS NOT NULL
ORDER  BY dateto

aslida tasodifiy qatorni qaytaradi. Menimcha, bu siz xohlagan narsa emas. Ehtimol, birinchi navbatda ORDER BY ni qo'llashni xohladingiz va keyin birinchi qatorni qabul qildingiz. Bu pastki so'rovlar foydali bo'lishi mumkin:

SELECT subject
FROM   (SELECT subject
        FROM   mdkndlst
        WHERE  subject IS NOT NULL
        ORDER  BY dateto)
WHERE  rownum = 1

va siz hozirda skalar subquery sifatida foydalanishingiz mumkin

SELECT mdkamvp.mdkamda_sid
      ,(SELECT subject
        FROM   (SELECT subject
                FROM   mdkndlst
                WHERE  subject IS NOT NULL
                ORDER  BY dateto)
        WHERE  rownum = 1) naktion
      ,mdkamvp.rowid row_id
FROM   springv2.mdkamvp
ORDER  BY mdkamvp.indstatus
1
qo'shib qo'ydi

rownum satrini belgilash ORDER BY so'zini qo'llashdan oldin amalga oshiriladi, shuning uchun bu so'rov

SELECT subject
FROM   mdkndlst
WHERE  rownum = 1
       AND subject IS NOT NULL
ORDER  BY dateto

aslida tasodifiy qatorni qaytaradi. Menimcha, bu siz xohlagan narsa emas. Ehtimol, birinchi navbatda ORDER BY ni qo'llashni xohladingiz va keyin birinchi qatorni qabul qildingiz. Bu pastki so'rovlar foydali bo'lishi mumkin:

SELECT subject
FROM   (SELECT subject
        FROM   mdkndlst
        WHERE  subject IS NOT NULL
        ORDER  BY dateto)
WHERE  rownum = 1

va siz hozirda skalar subquery sifatida foydalanishingiz mumkin

SELECT mdkamvp.mdkamda_sid
      ,(SELECT subject
        FROM   (SELECT subject
                FROM   mdkndlst
                WHERE  subject IS NOT NULL
                ORDER  BY dateto)
        WHERE  rownum = 1) naktion
      ,mdkamvp.rowid row_id
FROM   springv2.mdkamvp
ORDER  BY mdkamvp.indstatus
1
qo'shib qo'ydi

rownum satrini belgilash ORDER BY so'zini qo'llashdan oldin amalga oshiriladi, shuning uchun bu so'rov

SELECT subject
FROM   mdkndlst
WHERE  rownum = 1
       AND subject IS NOT NULL
ORDER  BY dateto

aslida tasodifiy qatorni qaytaradi. Menimcha, bu siz xohlagan narsa emas. Ehtimol, birinchi navbatda ORDER BY ni qo'llashni xohladingiz va keyin birinchi qatorni qabul qildingiz. Bu pastki so'rovlar foydali bo'lishi mumkin:

SELECT subject
FROM   (SELECT subject
        FROM   mdkndlst
        WHERE  subject IS NOT NULL
        ORDER  BY dateto)
WHERE  rownum = 1

va siz hozirda skalar subquery sifatida foydalanishingiz mumkin

SELECT mdkamvp.mdkamda_sid
      ,(SELECT subject
        FROM   (SELECT subject
                FROM   mdkndlst
                WHERE  subject IS NOT NULL
                ORDER  BY dateto)
        WHERE  rownum = 1) naktion
      ,mdkamvp.rowid row_id
FROM   springv2.mdkamvp
ORDER  BY mdkamvp.indstatus
1
qo'shib qo'ydi

you should use first_value

SELECT mdkamvp.mdkamda_sid,

  (SELECT DISTINCT first_value(subject) over (partition BY subject
                                              ORDER BY dateto DESC)
   FROM mdkndlst
   WHERE subject IS NOT NULL ) naktion,
       mdkamvp.rowid row_id
FROM springv2.mdkamvp
ORDER BY mdkamvp.indstatus
0
qo'shib qo'ydi

Tanlangan ro'yxatda skaler pastki so'rovni ishlatish o'rniga buyurtma bilan cheklashlarga ega bo'lmagan quyidagi jadvalni ishlating:

SELECT MDKAMVP.MDKAMDA_SID, 
       x.subject as NAKTION, 
       MDKAMVP.ROWID ROW_ID 
FROM SPRINGV2.MDKAMVP 
  cross join (
    select SUBJECT 
    from mdkndlst 
    where rownum=1 
      and SUBJECT is not null 
    order by DATETO DESC
  ) x
order by MDKAMVP.INDSTATUS;

Boshlang'ich jadval bitta qatorni qaytarish kafolatlanganligi bois cross cross umumiy natijani o'zgartirmaydi.

Agar siz so'nggi mavzusini xohlasangiz, siz DATETO tushgan buyurtma berishingiz kerak. Siz bo'lmasa, eng qadimgi mavzuni olasiz.

Agar siz 12c dan foydalansangiz, Jiri ko'rsatganidek Get first ni ishlatish mumkin.

0
qo'shib qo'ydi

Tanlangan ro'yxatda skaler pastki so'rovni ishlatish o'rniga buyurtma bilan cheklashlarga ega bo'lmagan quyidagi jadvalni ishlating:

SELECT MDKAMVP.MDKAMDA_SID, 
       x.subject as NAKTION, 
       MDKAMVP.ROWID ROW_ID 
FROM SPRINGV2.MDKAMVP 
  cross join (
    select SUBJECT 
    from mdkndlst 
    where rownum=1 
      and SUBJECT is not null 
    order by DATETO DESC
  ) x
order by MDKAMVP.INDSTATUS;

Boshlang'ich jadval bitta qatorni qaytarish kafolatlanganligi bois cross cross umumiy natijani o'zgartirmaydi.

Agar siz so'nggi mavzusini xohlasangiz, siz DATETO tushgan buyurtma berishingiz kerak. Siz bo'lmasa, eng qadimgi mavzuni olasiz.

Agar siz 12c dan foydalansangiz, Jiri ko'rsatganidek Get first ni ishlatish mumkin.

0
qo'shib qo'ydi

Tanlangan ro'yxatda skaler pastki so'rovni ishlatish o'rniga buyurtma bilan cheklashlarga ega bo'lmagan quyidagi jadvalni ishlating:

SELECT MDKAMVP.MDKAMDA_SID, 
       x.subject as NAKTION, 
       MDKAMVP.ROWID ROW_ID 
FROM SPRINGV2.MDKAMVP 
  cross join (
    select SUBJECT 
    from mdkndlst 
    where rownum=1 
      and SUBJECT is not null 
    order by DATETO DESC
  ) x
order by MDKAMVP.INDSTATUS;

Boshlang'ich jadval bitta qatorni qaytarish kafolatlanganligi bois cross cross umumiy natijani o'zgartirmaydi.

Agar siz so'nggi mavzusini xohlasangiz, siz DATETO tushgan buyurtma berishingiz kerak. Siz bo'lmasa, eng qadimgi mavzuni olasiz.

Agar siz 12c dan foydalansangiz, Jiri ko'rsatganidek Get first ni ishlatish mumkin.

0
qo'shib qo'ydi