[RESOLVED] How to get top 3 on the counted column

[BONITO]

how can i get the top 3, after i count the column ?

here is my code. to count the no. of the column Total_Fail.

select  count(UUT_SN) as Total_Fail,Fail_Description,Station_No from Muscatel_MPCA_FCT1_LogData_T 
WHERE SQLDateTime >= '2018-07-01'
AND SQLDateTime <= '2018-07-13 'AND Station_No in (19,20,21,22,23,24)and Fail_Description NOT LIKE '%[PASS]%'
group by Fail_Description,Station_No
ORDER BY Total_Fail DESC

this will give result as below.

Total_Fail Fail_Description Station_No
8 RR [79] 22
6 RR [79] 20
6 RR [81] 22
6 RR [80] 23
4 RR [80] 22
2 RR [79] 19
2 RR [80] 19
2 RR [80] 20
2 RR [81] 20
2 RR [81] 21
2 RR [78] 23
2 RR [79] 23
2 RR [80] 24
1 RR [6] 24


but i just want to get the top 3 ,how can i do that ?

[jmcilhinney]

I just typed "sql server get top 3 records" into a Bing search and I had the answer to your question in about five seconds, if that. I have to wonder why you couldn't do that.

[BONITO]

what is Bing Search,
i try to do it like this but it seems it gives me not correct data. when i used select top 3 to a counted column.

select  top 3 count(UUT_SN) as Total_Fail,Fail_Description,Station_No from Muscatel_MPCA_FCT1_LogData_T 
WHERE SQLDateTime >= '2018-07-01'
AND SQLDateTime <= '2018-07-13 'AND Station_No in (19,20,21,22,23,24)and Fail_Description NOT LIKE '%[PASS]%'
group by Fail_Description,Station_No
ORDER BY Total_Fail DESC

[si_the_geek]

Try this:

SELECT TOP 3 *
FROM (
   < your original query here >
)

(you may need to add an alias)

what is Bing Search,

One of the alternatives to Google search

[jmcilhinney]

One of the alternatives to Google search

I find that Bing tends to give better results than Google for .NET development questions.

[szlamany]

Row_Number() could be used for this if you find that you are having issues with the TOP 3 syntax.

[BONITO]

Try this:

SELECT TOP 3 *
FROM (
   < your original query here >
)

(you may need to add an alias)

One of the alternatives to Google search

hi Sir,

I try this one but have error.

select top 3 * from (select count(UUT_SN) as Total_Fail,Fail_Description,Station_No from Muscatel_MPCA_FCT1_LogData_T  
WHERE SQLDateTime >= '2018-07-01' AND SQLDateTime <= '2018-07-13' AND Station_No in (19,20,21,22,23,24)and Fail_Description NOT LIKE '%[PASS]%' 
group by Fail_Description,Station_No)

incorrect syntax near ')'-

[topshot]

Unless they have added it recently, Oracle (which I thought is what you're using) doesn't support the TOP statement. Depending on which version you're using depends on whether you'll use ROWNUM or FETCH clause. This article can get you started.

[FunkyDexter]

[Rant]Why is it that perfectly well disciplined programmers who will rabidly format their code immediately throw away all the rules when working with sql and present a blobby mess. God, it makes things difficult to debug[/Rant]

incorrect syntax near ')'

The sub query has to have an alias. Don't know why but those are the rules.

select top 3 * 
from (select count(UUT_SN) as Total_Fail,Fail_Description,Station_No 
    from Muscatel_MPCA_FCT1_LogData_T  
    WHERE SQLDateTime >= '2018-07-01' 
    AND SQLDateTime <= '2018-07-13' 
    AND Station_No in (19,20,21,22,23,24)
    and Fail_Description NOT LIKE '%[PASS]%' 
    group by Fail_Description, Station_No) T

Also, the Top keyword is pretty meaningless without an order by. So you really want:-

select top 3 * 
from (select count(UUT_SN) as Total_Fail,Fail_Description,Station_No 
    from Muscatel_MPCA_FCT1_LogData_T  
    WHERE SQLDateTime >= '2018-07-01' 
    AND SQLDateTime <= '2018-07-13' 
    AND Station_No in (19,20,21,22,23,24)
    and Fail_Description NOT LIKE '%[PASS]%' 
    group by Fail_Description, Station_No) T
Order By Total_Fail

[szlamany]

[Rant]Why is it that perfectly well disciplined programmers who will rabidly format their code immediately throw away all the rules when working with sql and present a blobby mess. God, it makes things difficult to debug[/Rant]

The sub query has to have an alias. Don't know why but those are the rules.

+1 for the rant!

As for the alias, I always assumed that a FROM clause has to introduce an object with a name. When you put a sub-query into a SELECT list it gets a "(No column name)" (which I imagine is some kind of NULL probably).

The FROM needs to have the ability to have things JOIN to it - so it must be named.

[FunkyDexter]

I always assumed that a FROM clause has to introduce an object with a name

Yeah, that makes sense. I guess I look at it and think "why do you need that name? you're not doing anything with it" but the query parser is looking at it saying "I want a name, I might so something with it"