CODEWITHSUNDEEP
sqlsqlite
verideskstar
verideskstar

Reputation: 187

SQL query to get rows with a preference based on row value

I have a table that can have multiple records for ip_address. However, I want to return rows where canceled=0 if multiple records of ip_address exist, otherwise get the first row where canceled=1

id  ip_address  username  canceled
0   10.10.10.1  john      0
1   10.10.10.1  johnny    1
2   10.10.10.1  quincy    1
3   10.10.10.2  bob       1
4   10.10.10.3  kristin   0

Examples returned resultset for SELECT ip_address,username ....:

10.10.10.1  john
10.10.10.2  bob
10.10.10.3  kristin

Using SQLAlchemy/Sqlite if that is of any relevance.

SELECT 
  b.id,
  a.ip_address,
  b.username,
  b.canceled
FROM (
  select distinct(ip_address) from mytable order by canceled) a,
  mytable b
WHERE
  b.ip_address=a.ip_address;

But that is returning multiple definitions for "ip_address"

Upvotes: 0

Views: 225

Answers (2)

Cs 8128
Cs 8128

Reputation: 113

Assuming canceled is an int,this should work.

select *from #temp order by canceled
with cte as 
(
select ip_address,username,canceled,ROW_NUMBER() over(partition by  ip_address order by canceled) as rn from #temp
)
select *from cte where rn=1

Upvotes: 0

verideskstar
verideskstar

Reputation: 187

The proper query appears to be:

SELECT b.id,a.ip_address,b.username,b.canceled FROM (select distinct(ip_address) from mytable order by canceled) a, mytable b WHERE b.ip_address=a.ip_address GROUP BY b.ip_address

Upvotes: 1

Related Questions