siddharth
Reputation: 284
How to get unique data in SQL
I have tried distinct but it is not working in my case.
I have following table with data :
qincId ID lc1 lc2 Time SP
------------------------------------------------------------------------
963 544 22.3000526428 73.1743087769 2019-03-31 17:00:46.000 15
965 544 22.2998828888 73.1746368408 2019-03-31 17:01:07.000 2
968 544 22.2998828888 73.1746368408 2019-03-31 17:01:40.000 2
997 544 22.3010215759 73.1744003296 2019-03-31 17:06:11.000 15
998 544 22.3011436462 73.1747131348 2019-03-31 17:06:21.000 17
1010 544 22.3034667969 73.1747512817 2019-03-31 17:08:04.000 0
1011 544 22.3032741547 73.1747512817 2019-03-31 17:08:03.000 0
1012 544 22.3032741547 73.1747512817 2019-03-31 17:08:04.000 0
1028 544 22.3032741547 73.1747512817 2019-03-31 17:11:04.000 0
1563 544 22.3032741547 73.1747512817 2019-03-31 18:45:27.000 0
1564 544 22.3032741547 73.1747512817 2019-03-31 18:45:28.000 0
1565 544 22.3032035828 73.1748123169 2019-03-31 18:45:26.000 0
1567 544 22.3032035828 73.1748123169 2019-03-31 18:45:28.000 0
1571 544 22.3028964996 73.1748123169 2019-03-31 18:46:03.000 16
1573 544 22.3023796082 73.1747131348 2019-03-31 18:46:21.000 15
1575 544 22.3021774292 73.1746444702 2019-03-31 18:46:37.000 0
1577 544 22.3019657135 73.1747665405 2019-03-31 18:46:50.000 15
1586 544 22.3009243011 73.1742477417 2019-03-31 18:47:33.000 5
1591 544 22.2998828888 73.1745300293 2019-03-31 18:48:19.000 5
1592 544 22.2998828888 73.1745300293 2019-03-31 18:48:28.000 5
1593 544 22.2998981476 73.1746063232 2019-03-31 18:48:29.000 4
1597 544 22.3000450134 73.1744232178 2019-03-31 18:49:08.000 0
1611 544 22.3000450134 73.1744232178 2019-03-31 18:51:28.000 0
1616 544 22.3000450134 73.1744232178 2019-03-31 18:52:22.000 0
1677 544 22.3000450134 73.1744232178 2019-03-31 19:03:28.000 0
Now I want all records with a sp > 0 and 1st record with a speed = 0 with same lc1 and lc2.
So basically I don't want data whose lc1 and lc2 are repeating with sp = 0
Expected output from above records:
qincId ID lc1 lc2 Time SP
-------------------------------------------------------------------------
963 544 22.3000526428 73.1743087769 2019-03-31 17:00:46.000 15
965 544 22.2998828888 73.1746368408 2019-03-31 17:01:07.000 2
968 544 22.2998828888 73.1746368408 2019-03-31 17:01:40.000 2
997 544 22.3010215759 73.1744003296 2019-03-31 17:06:11.000 15
998 544 22.3011436462 73.1747131348 2019-03-31 17:06:21.000 17
1010 544 22.3034667969 73.1747512817 2019-03-31 17:08:04.000 0
1011 544 22.3032741547 73.1747512817 2019-03-31 17:08:03.000 0
1565 544 22.3032035828 73.1748123169 2019-03-31 18:45:26.000 0
1571 544 22.3028964996 73.1748123169 2019-03-31 18:46:03.000 16
1573 544 22.3023796082 73.1747131348 2019-03-31 18:46:21.000 15
1575 544 22.3021774292 73.1746444702 2019-03-31 18:46:37.000 0
1577 544 22.3019657135 73.1747665405 2019-03-31 18:46:50.000 15
1586 544 22.3009243011 73.1742477417 2019-03-31 18:47:33.000 5
1591 544 22.2998828888 73.1745300293 2019-03-31 18:48:19.000 5
1592 544 22.2998828888 73.1745300293 2019-03-31 18:48:28.000 5
1593 544 22.2998981476 73.1746063232 2019-03-31 18:48:29.000 4
1597 544 22.3000450134 73.1744232178 2019-03-31 18:49:08.000 0
1677 544 22.3000450134 73.1744232178 2019-03-31 19:03:28.000 0
I have tried with distinct and group by also but i am not able to get the output.
How can I get my expected output ?
Upvotes: 0
Views: 144
Answers (1)
Zaynul Abadin Tuhin
Reputation: 32021
try like below by using union all and row_number()
select qincId,ID,lc1,lc2,time,sp from
(select qincId,ID,lc1,lc2,time,sp,
row_number()over(partition by lc1,lc2 order by time) rn
from table_name where sp>0
) t where t.rn=1
union all
select qincId,ID,lc1,lc2,time,sp from
(
select *,row_number()over(partition by lc1,lc2 order by time ) rn
from table_name where sp=0
) a where a.rn=1
Upvotes: 2