CODEWITHSUNDEEP
sqlsqlite
user782104
user782104

Reputation: 13555

The SQL (SQLite) query in this situation?

There are two table

User
========
uid
name

Data
=========
distance
timeSpend
isShow
uid (FK)

I would like to get an order list (DESC) of Distance of the following citeria

1 ) in a specific timeSpend Range
2 ) group by the uid (Only select the longest Distance)
3 ) only isShow

Tried the following Query but no luck. Thanks for helping

SELECT User.name, Data.distance, Data.timeSpend
FROM FROM User,Data
WHERE id IN (
    SELECT MAX(distance) FROM Data GROUP BY uid WHERE isShow = true
    )
AND User.uid = Data.uid
ORDER BY Data.distance DESC

Upvotes: 0

Views: 27

Answers (1)

juergen d
juergen d

Reputation: 204854

You have to fill the between statement with the values you like

SELECT u.name, MAX(distance) as max_distance
FROM User u
join Data d on u.uid = d.uid
WHERE isShow = 1
and d.timeSpend between 1 and 2
group by u.uid, u.uname
ORDER BY max_distance DESC

Upvotes: 1

Related Questions