SQL top

[sacramento]

Hi:

I have this code in SQL,and I just want extract the 3 big's values from my database(ACcess) working in DAO....but the code extract all the values >='90' and not the only 3 big values >='90'

something wrong with code?

sql1 = _
     "select top 3 pontosind1 as Totais, classifind1 as Classifind, seccao1 as Secção, classe1 as Classe, designacao1 as Designação, nomesocio as Expositor, anilha1 as Anilha, gaiolaind1 as Gaiola, Sexo1 as Sexo" & _
     " from exposicao " & _
     " WHERE pontosind1 >= '90' & _
     "and (pontoseq1 = '' or pontoseq1 is null) " & _
     " UNION ALL" & _
     " select pontosind2, classifind2, seccao2, classe2, designacao2, nomesocio, anilha2, gaiolaind2, sexo2" & _
     " from exposicao " & _
     " WHERE pontosind2 >= '90' & _
     "and (pontoseq1 = '' or pontoseq1 is null) " & _
     " UNION ALL" & _
     " select pontosind3, classifind3, seccao3, classe3, designacao3, nomesocio, anilha3, gaiolaind3, sexo3" & _
     " from exposicao " & _
     " WHERE pontosind3 >= '90' & _
     "and (pontoseq1 = '' or pontoseq1 is null) " & _
     " UNION ALL" & _
     " select pontosind4, classifind4, seccao4, classe4, designacao4, nomesocio, anilha4, gaiolaind4, sexo4" & _
     " from exposicao " & _
     " WHERE pontosind4 >= '90' & _
     "and (pontoseq1 = '' or pontoseq1 is null) " & _
     " ORDER BY classe desc,Totais desc "

thanks

[brucevde]

The Select Top 3 is applied to the first query only. If you want to get the Top 3 from all queries in the union you will need to use a subquery. I am not sure if MSAccess supports subqueries.

sql1 = "Select Top 3 * From ( "_
     "select pontosind1 as Totais, classifind1 as Classifind, seccao1 as Secção, classe1 as Classe, designacao1 as Designação, nomesocio as Expositor, anilha1 as Anilha, gaiolaind1 as Gaiola, Sexo1 as Sexo" & _
     " from exposicao " & _
     " WHERE pontosind1 >= '90' & _
     "and (pontoseq1 = '' or pontoseq1 is null) " & _
     " UNION ALL" & _
     " select pontosind2, classifind2, seccao2, classe2, designacao2, nomesocio, anilha2, gaiolaind2, sexo2" & _
     " from exposicao " & _
     " WHERE pontosind2 >= '90' & _
     "and (pontoseq1 = '' or pontoseq1 is null) " & _
     " UNION ALL" & _
     " select pontosind3, classifind3, seccao3, classe3, designacao3, nomesocio, anilha3, gaiolaind3, sexo3" & _
     " from exposicao " & _
     " WHERE pontosind3 >= '90' & _
     "and (pontoseq1 = '' or pontoseq1 is null) " & _
     " UNION ALL" & _
     " select pontosind4, classifind4, seccao4, classe4, designacao4, nomesocio, anilha4, gaiolaind4, sexo4" & _
     " from exposicao " & _
     " WHERE pontosind4 >= '90' & _
     "and (pontoseq1 = '' or pontoseq1 is null)) " & _
     " ORDER BY classe desc,Totais desc "

[szlamany]

I also do not use ACCESS, but if you have a problem with the SUBQUERY, maybe make a VIEW of the QUERY with the UNION's and then refer to that VIEW with the SELECT TOP 3...

[sacramento]

can you verify the code...I Got a error in the syntax

Thanks

[dee-u]

What's the description of the error raised?

[dglienna]

You could paste it into Access and execute it. It should give a descriptive error message.

[sacramento]

you can try with the attached project

[dee-u]

You just missed some & and "...

sql1 = "Select Top 3 * From ( " & _
     "select pontosind1 as Totais, classifind1 as Classifind, seccao1 as Secção, classe1 as Classe, designacao1 as Designação, nomesocio as Expositor, anilha1 as Anilha, gaiolaind1 as Gaiola, Sexo1 as Sexo" & _
     " from exposicao " & _
     " WHERE pontosind1 >= '90'" & _
     " and (pontoseq1 = '' or pontoseq1 is null) " & _
     " UNION ALL" & _
     " select pontosind2, classifind2, seccao2, classe2, designacao2, nomesocio, anilha2, gaiolaind2, sexo2" & _
     " from exposicao " & _
     " WHERE pontosind2 >= '90'" & _
     " and (pontoseq1 = '' or pontoseq1 is null) " & _
     " UNION ALL" & _
     " select pontosind3, classifind3, seccao3, classe3, designacao3, nomesocio, anilha3, gaiolaind3, sexo3" & _
     " from exposicao " & _
     " WHERE pontosind3 >= '90'" & _
     " and (pontoseq1 = '' or pontoseq1 is null) " & _
     " UNION ALL" & _
     " select pontosind4, classifind4, seccao4, classe4, designacao4, nomesocio, anilha4, gaiolaind4, sexo4" & _
     " from exposicao " & _
     " WHERE pontosind4 >= '90'" & _
     " and (pontoseq1 = '' or pontoseq1 is null)) " & _
     " ORDER BY classe desc,Totais desc "

[sacramento]

yes you are right...now the code work,but the "top 3" don't extract the desired values because the top 3 extract only 3 recordsets and not all the 3 big recordsets....strange!!!!!!!


Can you see the project attached,and run it,and change the top 3 for top 10?


Thanks

[szlamany]

You are missing an ORDER BY outside the subquery...

SELECT TOP 3 * FROM (...) ORDER BY TOTAIS DESC

Without that you are not going to get the proper top 3 - numerically highest.

[sacramento]

sory but I don't understand...I have a order outside of the subquery...I Think!!!!

do you have see the project?

Thnaks

[szlamany]

Yes - I do see that you have an ORDER BY...

But you are ordering by the CLASS DESCRIPTION - right? So you are getting the alphabetically first 3 classes - is that what is happening?

Don't you want the 3-highest grades?

ORDER BY TOTAIS should do that for you...

[sacramento]

But you are ordering by the CLASS DESCRIPTION - right? So you are getting the alphabetically first 3 classes - is that what is happening?

yes that correct...I get the first 3 classes alphabetically

Don't you want the 3-highest grades?

yes I want the highest 3 grades but ordering by classe and totais...I want the 3 highest values ordering by classe and listening by totais....but the 3 highest values should be By totais not classe

That's possible do this????

[szlamany]

Well since you are already going down the path of sub-query...

You need to do the TOP 3/ORDER BY TOTAIS - there is no way around that.

If you want them to then be ordered by CLASS DESCRIPTION, then I guess you put that in another SUBQUERY - order by class description...

SELECT * FROM (SELECT TOP 3 * FROM (...) ORDER BY TOTAIS DESC) 
ORDER BY CLASSE

[sacramento]

Maybe like this,but the sort are not right,unless the syntax are not correct

sql1 = "Select Top 3 * From ( " & _
     "select pontosind1 as Totais, classifind1 as Classifind, seccao1 as Secção, classe1 as Classe, designacao1 as Designação, nomesocio as Expositor, anilha1 as Anilha, gaiolaind1 as Gaiola, Sexo1 as Sexo" & _
     " from exposicao " & _
     " WHERE pontosind1 >= '88'" & _
     " and (pontoseq1 = '' or pontoseq1 is null) " & _
     " UNION ALL" & _
     " select pontosind2, classifind2, seccao2, classe2, designacao2, nomesocio, anilha2, gaiolaind2, sexo2" & _
     " from exposicao " & _
     " WHERE pontosind2 >= '88'" & _
     " and (pontoseq1 = '' or pontoseq1 is null) " & _
     " UNION ALL" & _
     " select pontosind3, classifind3, seccao3, classe3, designacao3, nomesocio, anilha3, gaiolaind3, sexo3" & _
     " from exposicao " & _
     " WHERE pontosind3 >= '88'" & _
     " and (pontoseq1 = '' or pontoseq1 is null) " & _
     " UNION ALL" & _
     " select pontosind4, classifind4, seccao4, classe4, designacao4, nomesocio, anilha4, gaiolaind4, sexo4" & _
     " from exposicao " & _
     " WHERE pontosind4 >= '88'" & _
     " and (pontoseq1 = '' or pontoseq1 is null)" & _
     [B]" ORDER BY totais desc) " & _
     " ORDER BY classe desc [/B] "

thanks

[szlamany]

I said to add another sub-query - not sort the "primary sub-query".

sql1 = "Select * From (Select Top 3 * From ( " & _
     "select pontosind1 as Totais, classifind1 as Classifind, seccao1 as Secção, classe1 as Classe, designacao1 as Designação, nomesocio as Expositor, anilha1 as Anilha, gaiolaind1 as Gaiola, Sexo1 as Sexo" & _
     " from exposicao " & _
     " WHERE pontosind1 >= '88'" & _
     " and (pontoseq1 = '' or pontoseq1 is null) " & _
     " UNION ALL" & _
     " select pontosind2, classifind2, seccao2, classe2, designacao2, nomesocio, anilha2, gaiolaind2, sexo2" & _
     " from exposicao " & _
     " WHERE pontosind2 >= '88'" & _
     " and (pontoseq1 = '' or pontoseq1 is null) " & _
     " UNION ALL" & _
     " select pontosind3, classifind3, seccao3, classe3, designacao3, nomesocio, anilha3, gaiolaind3, sexo3" & _
     " from exposicao " & _
     " WHERE pontosind3 >= '88'" & _
     " and (pontoseq1 = '' or pontoseq1 is null) " & _
     " UNION ALL" & _
     " select pontosind4, classifind4, seccao4, classe4, designacao4, nomesocio, anilha4, gaiolaind4, sexo4" & _
     " from exposicao " & _
     " WHERE pontosind4 >= '88'" & _
     " and (pontoseq1 = '' or pontoseq1 is null))" & _
     " ORDER BY totais desc) " & _
     " ORDER BY classe desc  "

[sacramento]

I think this is a big problem....the code don't extract the desired values

If you can try the project please...the code work but don't extract all values

Thanks