How do I select the ID immediately less than the current one

Databases
1

Hi Guys,

I use the query below and it always return the least value if it has more than one:

SELECT id, uid, active
FROM member
WHERE id < :xx AND uid = :yy AND active != '0'
LIMIT 1

Language: MySQL, PHP

Thanks,

2

Add a ORDER BY id DESC?

3

What it comes to mind

SELECT MIN(id), uid, active
FROM member 
WHERE MIN(id)<  :XX AND uid= :yy AND active != 0
GROUP BY uid, active

I don’t know if this would work (I’m having a particularly silly day) but it is what it comes to my mind now

edit: I used MAX but I should have written MIN since you want the least (Lower) one

4

actually, without an ORDER BY, it can return ~any~ row that meets the WHERE conditions

what do you mean by “the current one” ??

5

First sorry,

The code above is query the PHOTO table not MEMBER. I din’t notice about it.

what do you mean by “the current one” ??

Suppose the query above was LIMIT 6, the result looks like this:

2 4 6 8 10 12

Then when I pick some ID as below:

id (:xx) = 8, uid (:yy) = 5

The immediate ID less than that one should be 6, but I always got 2, no matter the ID I pick is 6, 8, 10, 12.

I use ORDER BY DESC and it still doesn’t work.

I use the same code to query for the greater ID and it works fine. I just change from > to < .

Hope I explain it clearly.

Thanks,

6

[quote=“ketting00, post:5, topic:115650, full:true”]
I use ORDER BY DESC and it still doesn’t work.[/quote]

did you try it like this?

SELECT id , uid , active FROM member WHERE id < :xx AND uid = :yy AND active != '0' ORDER BY id DESC LIMIT 1

if this still doesn’t work, i would like you to run the query without the AND uid = :yy

maybe the 2 that you’re returning is the largest id value that has uid=5

7

This is my thoughts too. Can you provide both the ID, UID and Active columns for those 6 entries?

8

It works.

Thank you.

9

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.