July 16, 2018 at 6:34 am
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 Test_table
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 ?
July 16, 2018 at 6:48 am
BONITO - Monday, July 16, 2018 6:34 AMhow 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 DESCthis 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] 24but i just want to get the top 3 ,how can i do that ?
Have you tried using TOP (3) between the SELECT and the column list?
July 16, 2018 at 6:57 am
select top 3 count(UUT_SN) as Total_Fail,Fail_Description,Station_No from Test_table
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
but it didn't give a correct ourput.
July 16, 2018 at 7:29 am
BONITO - Monday, July 16, 2018 6:57 AMselect top 3 count(UUT_SN) as Total_Fail,Fail_Description,Station_No from Test_table
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
but it didn't give a correct ourput.
What would be the correct output? Can you share sample data and expected results?
July 16, 2018 at 7:29 am
BONITO - Monday, July 16, 2018 6:57 AMselect top 3 count(UUT_SN) as Total_Fail,Fail_Description,Station_No from Test_table
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
but it didn't give a correct ourput.
Your example has a three-way tie for second place, and you haven't specified what you want to do in the case of ties. You either need to include all of the tied records using the WITH TIES keyword, or you need to specify additional columns in your ORDER BY clause to break the tie.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 16, 2018 at 7:40 am
BONITO - Monday, July 16, 2018 6:57 AMselect top 3 count(UUT_SN) as Total_Fail,Fail_Description,Station_No from Test_table
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
but it didn't give a correct ourput.
What would be the correct output? Can you share sample data and expected results?
I already share on my first post, but my first query i not use Top 3 that's why my result was all data.
my second sample i add top 3 code on my select. but it didn't give a correct top 3.
i expect my output to be looks like this.
show the top 3 base on the total_fail.
Total_Fail Fail_Description Station_No
80 RR [79] 22
60 RR [79] 20
50 RR [81] 22
July 16, 2018 at 7:42 am
drew.allen - Monday, July 16, 2018 7:29 AMBONITO - Monday, July 16, 2018 6:57 AMselect top 3 count(UUT_SN) as Total_Fail,Fail_Description,Station_No from Test_table
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
but it didn't give a correct ourput.Your example has a three-way tie for second place, and you haven't specified what you want to do in the case of ties. You either need to include all of the tied records using the WITH TIES keyword, or you need to specify additional columns in your ORDER BY clause to break the tie.
Drew
can you please modify my code . because i am a newbie. don't know how to get the top 3 records on the counted column.
July 16, 2018 at 8:15 am
Luis Cazares - Monday, July 16, 2018 7:29 AMBONITO - Monday, July 16, 2018 6:57 AMselect top 3 count(UUT_SN) as Total_Fail,Fail_Description,Station_No from Test_table
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
but it didn't give a correct ourput.What would be the correct output? Can you share sample data and expected results?
I already share on my first post, but my first query i not use Top 3 that's why my result was all data.
my second sample i add top 3 code on my select. but it didn't give a correct top 3.
i expect my output to be looks like this.
show the top 3 base on the total_fail.
Total_Fail Fail_Description Station_No
80 RR [79] 22
60 RR [79] 20
50 RR [81] 22
The code that you posted including the top 3 gives the top 3 rows. We don't know why it's not the correct result. We can't know what's the problem unless you explain it. That's why we need sample data and expected results based on the sample data.
This is an example on how the code actually works (with the generation of sample data).
CREATE TABLE Test_table(
Fail_Description VARCHAR(10),
Station_No INT,
UUT_SN INT,
SQLDateTime DATETIME);
WITH CTE AS (
SELECT *
FROM (VALUES
(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))s(Total_Fail, Fail_Description, Station_No)
)
INSERT INTO dbo.Test_table
(
Fail_Description,
Station_No,
UUT_SN,
SQLDateTime
)
SELECT CTE.Fail_Description,
CTE.Station_No,
t.n,
'2018-07-05'
FROM CTE
JOIN (VALUES(1),(2),(3),(4),(5),(6),(7),(8)) t(n) ON cte.Total_Fail >= t.n;
--This is the code that you posted.
select top 3 count(UUT_SN) as Total_Fail,Fail_Description,Station_No from Test_table
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
GO
DROP TABLE dbo.Test_table
July 16, 2018 at 8:19 am
BONITO - Monday, July 16, 2018 7:42 AMdrew.allen - Monday, July 16, 2018 7:29 AMBONITO - Monday, July 16, 2018 6:57 AMselect top 3 count(UUT_SN) as Total_Fail,Fail_Description,Station_No from Test_table
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
but it didn't give a correct ourput.Your example has a three-way tie for second place, and you haven't specified what you want to do in the case of ties. You either need to include all of the tied records using the WITH TIES keyword, or you need to specify additional columns in your ORDER BY clause to break the tie.
Drew
can you please modify my code . because i am a newbie. don't know how to get the top 3 records on the counted column.
Only 13 minutes between when I posted and when you gave up. In those 13 minutes you posted two separate replies. This suggests that you didn't even try to figure out my response. I prefer to help people who are interested in learning rather than being spoon-fed answers.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 16, 2018 at 9:02 am
Or possibly using RANK.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 16, 2018 at 8:00 pm
I got it , thanks for all the reply,
i review my code again and it is working fine now. select top 3 count(UUT_SN) as Total_Fail,Fail_Description,Station_No from Test_table
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
July 16, 2018 at 8:22 pm
BONITO - Monday, July 16, 2018 8:00 PMI got it , thanks for all the reply,i review my code again and it is working fine now.
select top 3 count(UUT_SN) as Total_Fail,Fail_Description,Station_No from Test_table
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
Ummm... what about the "ties:?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 17, 2018 at 2:28 am
with cte as
(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
)
select top 3 * from cte
order by Total_Fail desc
***The first step is always the hardest *******
July 17, 2018 at 6:12 am
Folks are still forgetting about "ties". If you have a dozen ties for 1st, 2nd, or 3rd, is it actually appropriate to ignore such ties or should (must) they be considered?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 17, 2018 at 6:19 am
Jeff Moden - Tuesday, July 17, 2018 6:12 AMFolks are still forgetting about "ties". If you have a dozen ties for 1st, 2nd, or 3rd, is it actually appropriate to ignore such ties or should (must) they be considered?
Sometimes, you only have space for 3. Is it appropriate? Probably not.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply