Don't show rank if dosn't exist in database id

Question

how to not show rank if dosn't exist in database id in database

SQL code:

$query = "SELECT id, points, count(*)+1 AS rank FROM weekly_points 
          WHERE points > (SELECT points FROM weekly_points 
            WHERE id='".$account['id']."'
    )";

Example query for id 15000 which doesn't exist:

SELECT id,points,count(*)+1 as rank 
FROM weekly_points
WHERE points > (SELECT points FROM weekly_points WHERE id='15000')
-- output is rank 1 which is wrong

For id doesn't exist it shows rank 1 but I want to not show the rank or to be 9999999 or last place

Sample,for rank to show

uid------points-----> rank

1----------100-----5

2----------200------4

3----------1000---- 1

4----------300-----3

5----------400-----2

[How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Dharman, Dec 29 '18 at 23:44
[The Hitchhiker's Guide to SQL Injection prevention](https://phpdelusions.net/sql_injection) — Dharman, Dec 29 '18 at 23:45
the issue is not about sql injection but about id if dosn't exist on database to show something else , but thanks for telling me how to prevent sql injection — OrWeCanDoIT, Dec 29 '18 at 23:49
Can you add some sample data of what your current query is returning right now and what you expect to get? — Shidersz, Dec 30 '18 at 00:10
@Shidersz Yes i can, for example if we take id 15000 which dosnt exist is giving me **rank 1** i expected to not show becuse it dosnt exist on database or to show **last place** mabye could be 99999 — OrWeCanDoIT, Dec 30 '18 at 10:11
@OrWeCanDoIT what I asked for is a set of data belonging to the `weekly_points` table and the output you are getting using your query. That sample you give is the same that was on your original question. — Shidersz, Dec 30 '18 at 15:29
Your query doesn't look like a normal rank query. Why doesn't it have `GROUP BY id`? Which `id` and `points` is it supposed to return? — Barmar, Jan 01 '19 at 01:23
yes this should return the rank of that uid example uid:1-- 1200 points -- rank 1,uid:2-- 300 points -- rank 2,uid:3-- 200 points -- rank 3 — OrWeCanDoIT, Jan 01 '19 at 01:29
is `id` an integer? or a string? i.e. why are you using quotes for `id = '1500'`? I think you need to provide some **sample data** and the **expected result** from that sample . I suggest you read these: [Provide a `Minimal Complete Verifiable Example` (MCVE)](https://stackoverflow.com/help/mcve) and [Why should I provide a MCVE](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Paul Maxwell, Jan 01 '19 at 01:31
Welcome to Stack Overflow! Please do not vandalize your posts. By posting on the Stack Exchange network, you've granted a non-revocable right for SE to distribute that content (under the [CC-BY-SA 3.0 license](//creativecommons.org/licenses/by-sa/3.0)). By SE policy, any vandalism will be reverted and subsequent attempts will get you banned. If you would like to disassociate this post from your account, see [What is the proper route for a disassociation request](//meta.stackoverflow.com/q/323395/584192)? — Samuel Liew, Jan 01 '19 at 02:20

score 3 · Answer 1 · edited Jan 01 '19 at 01:55

3

You can use CASE to return a default value when the ID doesn't exists.

SELECT id, points,
    CASE WHEN EXISTS (SELECT * FROM weekly_points WHERE id='15000')
         THEN COUNT(*) + 1
         ELSE 999999
    END AS rnk
FROM weekly_points
WHERE points > (SELECT points FROM weekly_points WHERE id='15000')

DEMO

It doesn't really make sense to include id and points in this query. It will simply pick them from unpredictable rows that have higher points than the given ID.

edited Jan 01 '19 at 01:55

Tim Biegeleisen

502,043
27
286
360

answered Jan 01 '19 at 01:26

Barmar

741,623
53
500
612

this looks good i need to make sure and i will do some testing – OrWeCanDoIT Jan 01 '19 at 01:32
if `id = '1500'` does not exist, the where clause subquery returns no row and the whole query returns no rows - or am I missing something? – Paul Maxwell Jan 01 '19 at 01:33
@Used_By_Already A query that contains an aggregate function in the select list always returns a single row. – Barmar Jan 01 '19 at 01:36
where is the aggregate function **in the where clause**? – Paul Maxwell Jan 01 '19 at 01:37
yes, but the where clause still makes no sense, if id 1500 does not exist it returns null, and greater than null makes no sense – Paul Maxwell Jan 01 '19 at 01:39
It doesn't matter, because we're not counting anything in that case, we just return `999999`. I've added a working demo. – Barmar Jan 01 '19 at 01:40
That demo just shows as blank - but OK I get it now. Sorry – Paul Maxwell Jan 01 '19 at 01:41
Click the Run button. – Barmar Jan 01 '19 at 01:42
The answer of @Barmar is correct according to what you have said, unless you haven't figured your code logic yet. – Tarreq Jan 01 '19 at 01:44
I know that. See the first comment I made to the question. – Barmar Jan 01 '19 at 01:46
@Barmar There is no run button visible. Perhaps you are logged-in? I am using Chrome as browser and have never seen that site's demos operate. The URL redirects to the startup screen of that site. i.e. a blank canvas – Paul Maxwell Jan 01 '19 at 01:47
@Used_By_Already No, I don't have an account there. I've only recently switched to it from sqlfiddle.com, but it seems to work fine for me with Chrome. The top toolbar has `Run Update Fork Load Example Collaborate`. – Barmar Jan 01 '19 at 01:49
1

Side note: `RANK` is a reserved keyword as of MySQL 8+, because it corresponds to the rank analytic function. You should avoid using `rank` as an alias. – Tim Biegeleisen Jan 01 '19 at 01:52
@TimBiegeleisen Wow, that really sucks. There are probably thousands of sites that do rank queries, and probably most of them use that alias, which will break if they upgrade. – Barmar Jan 01 '19 at 01:54
@Barmar, sorry again, no matter what I do that URL redirects to blank canvas, I suggest rextester.com or dbfiddle.uk (sqlfiddle is too unreliable imho) – Paul Maxwell Jan 01 '19 at 01:56
2

@Used_By_Already Don't know what to tell you. Tim had no problem getting to my fiddle and forking it into his own. – Barmar Jan 01 '19 at 01:59

Barbaros Özhan · Answer 2 · 2018-12-29T23:59:27.677

0

You can use EXISTS as in the following query

SELECT id,points,count(*)+1 as rank 
  FROM weekly_points p1
 WHERE EXISTS ( SELECT 1 
                  FROM weekly_points p2
                 WHERE p2.id='".$account['id']."'
                   AND p2.id=p1.id);

and then message to the screen that $account['id'] doesn't exists as a valid ID, if it doesn't return a value for id column in the select list.

edited Dec 29 '18 at 23:59

answered Dec 29 '18 at 23:49

Barbaros Özhan

59,113
10
31
55

for others existing id is not getting the rank , it getting the last rank – OrWeCanDoIT Dec 29 '18 at 23:56
@OrWeCanDoIT ok, reversely using `exists` may be a better way. In this case you can use those values in the select list, if they return. – Barbaros Özhan Dec 30 '18 at 00:00

score 0 · Answer 3 · answered Dec 30 '18 at 03:00

Probably the simplest method is to add a having clause:

SELECT count(*)+1 as rank 
FROM weekly_points
WHERE points > (SELECT points FROM weekly_points WHERE id = '15000')
HAVING SUM(id = '15000') > 0;

You should not be selecting other columns, because they are not part of the aggregation.

Tim Biegeleisen · Answer 4 · 2019-01-01T02:04:13.640

The only interpretation of your question which makes any sense to me is that you want to find the points ranking of some id, e.g. 15000. In this case, we can try first computing the number of points for that id in a separate subquery, and then aggregating over the entire table, counting records which have more points than this particular id.

SELECT
    CASE WHEN wp2.points IS NOT NULL
         THEN COUNT(CASE WHEN wp1.points > wp2.points THEN 1 END) + 1
         ELSE 999999 END rnk
FROM weekly_points wp1
CROSS JOIN (SELECT points FROM weekly_points WHERE id = '15000') wp2;

Using the same @Barmar demo:

Don't show rank if dosn't exist in database id

4 Answers4

Demo