PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
[RESOLVED] How to get top 3 on the counted column-VBForums
Results 1 to 11 of 11

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

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Mar 2018
    Posts
    83

    Resolved [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 ?

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,575

    Re: How to get top 3 on the counted column

    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.

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Mar 2018
    Posts
    83

    Re: How to get top 3 on the counted column

    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

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,020

    Re: How to get top 3 on the counted column

    Try this:
    Code:
    SELECT TOP 3 *
    FROM (
       < your original query here >
    )
    (you may need to add an alias)

    Quote Originally Posted by BONITO View Post
    what is Bing Search,
    One of the alternatives to Google search

  5. #5
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,575

    Re: How to get top 3 on the counted column

    Quote Originally Posted by si_the_geek View Post
    One of the alternatives to Google search
    I find that Bing tends to give better results than Google for .NET development questions.

  6. #6
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,847

    Re: How to get top 3 on the counted column

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

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  7. #7

    Thread Starter
    Lively Member
    Join Date
    Mar 2018
    Posts
    83

    Re: How to get top 3 on the counted column

    Quote Originally Posted by si_the_geek View Post
    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 ')'-

  8. #8
    Frenzied Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    1,152

    Re: How to get top 3 on the counted column

    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.

  9. #9
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,297

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

    [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.
    sql Code:
    1. SELECT top 3 *
    2. FROM (SELECT COUNT(UUT_SN) AS Total_Fail,Fail_Description,Station_No
    3.     FROM Muscatel_MPCA_FCT1_LogData_T  
    4.     WHERE SQLDateTime >= '2018-07-01'
    5.     AND SQLDateTime <= '2018-07-13'
    6.     AND Station_No IN (19,20,21,22,23,24)
    7.     AND Fail_Description NOT LIKE '%[PASS]%'
    8.     GROUP BY Fail_Description, Station_No) T

    Also, the Top keyword is pretty meaningless without an order by. So you really want:-
    SQL Code:
    1. SELECT top 3 *
    2. FROM (SELECT COUNT(UUT_SN) AS Total_Fail,Fail_Description,Station_No
    3.     FROM Muscatel_MPCA_FCT1_LogData_T  
    4.     WHERE SQLDateTime >= '2018-07-01'
    5.     AND SQLDateTime <= '2018-07-13'
    6.     AND Station_No IN (19,20,21,22,23,24)
    7.     AND Fail_Description NOT LIKE '%[PASS]%'
    8.     GROUP BY Fail_Description, Station_No) T
    9. ORDER BY Total_Fail
    Last edited by FunkyDexter; Jul 25th, 2018 at 09:20 AM.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  10. #10
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,847

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

    Quote Originally Posted by FunkyDexter View Post
    [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.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  11. #11
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,297

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

    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"
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width