CODEWITHSUNDEEP
sqlcountdistinct
SRY_JAVA
SRY_JAVA

Reputation: 323

Sql Query to retrieve unique rows with where clause and displaying row count

I have a table with following fields:-

device_name,
system_name,
alert_text,
direction,
arrival_time,
ack_time,
ack_status,
Currentval_analog,
Currentval_digital,
Lower_limit,
Upper_limit

I want to write a query which retrieves the unique or distinct arrival time and system name where ack_status=0 and direction=came.Arrival time for each system_name should be different. Along with it,I have to display the number of times the same system_name appears for different arrival_time.

Actually in my database there exist several system_names corresponding to each system_name there is arrival time and sometimes there exist redundancy between system name and arrival time i.e same arrival_time for the same_system.I want to remove this redundant row and count the total number of same system_name for different arrival_time corresponding to that system_name along with the where condition fulfilled.

Upvotes: 0

Views: 142

Answers (2)

Veera
Veera

Reputation: 3492

Try the below query. I think it will work.

;With cte AS
(
    SELECT Arrivaltime, system_name, ROW_NUMBER() OVER(PARTITION BY system_name 
       order BY system_name) 
     AS Rn FROM 
    (SELECT DISTINCT Arrivaltime, system_name FROM YourTableName 
    WHERE ack_status = 0 AND direction = 'came') AS A
)

SELECT A.Arrivaltime, A.system_name, (SELECT MAX(Rn) FROM Cte AS B 
WHERE A.system_name = B.system_name) 
AS Count FROM Cte AS A

SELECT arrival_time, system_name
FROM YourTableName WHERE ack_status = 0 AND direction = 'came'
GROUP BY arrival_time, system_name

Upvotes: 1

Suchit kumar
Suchit kumar

Reputation: 11859

SELECT arrival_time, system_name, COUNT(system_name) AS SystemCount 
FROM YourTableName WHERE ack_status = 0 AND direction = 'came'
GROUP BY arrival_time, system_name

Upvotes: 0

Related Questions