Problem in a "simple" query :\

Question

Post reply

Problem in a "simple" query :\

work.heko

SSC Enthusiast

Points: 151
More actions
January 8, 2008 at 10:58 am

#182421

Helo there!
Here is a part of a larger table:
accepted || X
--------------------
1 || 3
1 || 1
1 || 2
0 || 2
0 || 7
0 || 7
0 || 9
1 || 10
I need to get X which has max non-acceptable articles. (where ACCEPTED equals 0)
So i do:
SELECT X
FROM table
WHERE accepted = 0
GROUP BY X
ORDER BY COUNT(x) DESC;
I get 3 rows: 7, 9, 2 (which is correct, cause 7 has two zeros (acceptable = 0)..
How to select only first row (to get only 7)?
I need only one row because i need to nest this query into some larger one (where id = (this query))..
Database is Oracle...
Thanks!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

Why dont you try top and use derived query.

Select top 1 x from

(

SELECT X

FROM table

WHERE accepted = 0

GROUP BY X

ORDER BY COUNT(x) DESC) as a

-Roy

work.heko SSC Enthusiast Points: 151 More actions · Answer 2

Thanks for advice, but in line 1 (SELECT TOP 1 UDE_IDUDELEZENCA)

[UDE_IDUDELEZENCA is X in upper table] I get an error:

SELECT TOP 1 UDE_IDUDELEZENCA FROM

*

ERROR at line 1:

ORA-00923: FROM keyword not found where expected

Whole statement:

SQL> SELECT TOP 1 UDE_IDUDELEZENCA FROM

2 (

3 SELECT UDE_IDUDELEZENCA

4 FROM PRISPEVKI

5 WHERE ODOBREN = 0

6 GROUP BY PRISPEVKI.UDE_IDUDELEZENCA

7 ORDER BY COUNT(PRISPEVKI.UDE_IDUDELEZENCA) DESC)

Any ideas? Thanks for help!

work.heko SSC Enthusiast Points: 151 More actions · Answer 3

Ok i managed it with ROWNUM = 1 😉

QUREY:

SELECT UDE_IDUDELEZENCA FROM

(

SELECT UDE_IDUDELEZENCA

FROM PRISPEVKI

WHERE ODOBREN = 0

GROUP BY PRISPEVKI.UDE_IDUDELEZENCA

ORDER BY COUNT(PRISPEVKI.UDE_IDUDELEZENCA) DESC

)

WHERE ROWNUM = 1;

Maybe is the problem with Oracle?

Thanks again for help!

Regards, Heko

Piotr.Rodak SSCrazy Eights Points: 9376 More actions · Answer 4

after TOP 1 there has to be a * or field list

update: didn't notice that it was Oracle.. so it has to be ROWNUM then 😀

...and your only reply is slàinte mhath

Adam Haines SSC-Insane Points: 23217 More actions · Answer 5

I dont believe ORACLE can use TOP, you must use the RowNumber, as you have.

Your query should look like this though:

SELECT TOP 1 X

FROM @test-2

WHERE accepted = 0 and ROWNUM = 1

GROUP BY X

ORDER BY COUNT(x) DESC;

My blog: http://jahaines.blogspot.com

work.heko SSC Enthusiast Points: 151 More actions · Answer 6

Thanks for help! Yep, TOP invokes an error, so I'm sure Oracle doesn't support TOP... Nevertheless, I solved a problem... Thank again!