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

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.

Code:

---

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 ?

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.

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.

Code:

---

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

---

Try this:

Code:

---

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

---

(you may need to add an alias)

Quote:

---

Originally Posted by BONITO

what is Bing Search,

---

One of the alternatives to Google search

Quote:

---

Originally Posted by si_the_geek

One of the alternatives to Google search

---

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

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

Quote:

---

Originally Posted by si_the_geek

Try this:

Code:

---

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.

Code:

---

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 ')'-

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.

[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]

Quote:

---

incorrect syntax near ')'

---

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

sql Code:

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:-

SQL Code:

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

Quote:

---

Originally Posted by 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]

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.

Quote:

---

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"