sql query (oracle)

[xalia]

exw duo tables

to ena table pou legete projects kai exei

project_id(PK) employee_id

a e1

b e1

c e2

d e3

 

se ena alo table exei plirofories gia tous employees , ta onomata tous kai alla

 

auto pou thelw einai na brw pios employee exei analabaei ta perisotera project

mexri twra mporw na dw posa project exei o kathe employee me

 

select count(employee_id) as mycount , employee_id

from projects

group by employee_id

order by employee_id

 

etsi blepw posa project exei o kathe employee, alla thelw na blepw mono ton employee me ta perisotera project!

any help pls???

[stavrakis1821]

select top 1 employee_ID from projects order by count(employee_ID) desc

 

den to exw dokimasei pote me 'count' alla kapws etsi to vlepw na douleuei. mporeis panta na exeis akoma ena field omws na ta katagrafeis auta fantazomai.

[xalia]

euxaristw polu! tha to dokimasw!

[random]

νομιζω το "TOP" δεν παιζει σε oracle

 

εγω κανω αυτο:

>
SELECT *
FROM ( SELECT employee_id, count(1) Mycount
      FROM projects
      GROUP BY employee_id 
      ORDER BY count(1) DESC
     ) WHERE ROWNUM < 2;

 

 

προσοχή, δεν κάνουμε ποτέ αυτό:

SELECT employee_id, count(1) Mycount

FROM projects

WHERE ROWNUM < 2

GROUP BY employee_id

ORDER BY count(1) DESC;

η εντολή εκτελείτε χωρις σφάλμα, αλλα δεν φέρνει το σωστο αποτέλεσμα

[xalia]

ευχαριστώ πολύ random! respect!!!

[stavrakis1821]

true twra pou to koitw. to antistoixo einai ena nested query kai rownum opws sou egrapse kai o filos pio panw. kane kai kanena google search omws kai einai pantou auto to syntax ths oracle.