sql query for finding the next id in a table in a particular category
i have a table like this..
Categories table
-------------------
ID Cat_Name
1 ca开发者_Python百科t1
2 cat2
3 cat3
Stations table
------------------
ID Sta_Name Cat_ID
1 sta1 1
2 sta2 3
3 sta3 2
4 sta4 1
5 sta5 1
6 sta6 3
Now i need to i have the only the ID input as 1(i.e, first row first col val) so now i want the output 4
since the next item in that category is 4
Next item in the sense i got the 1,4,6,9 id's in category 1 so now i have the input 6 now output must be 9 as the next item of 6 in category 1 is 9 if i gave input 9 the output must return 1
If I understand a good method would be...
Considering just the "station table" and the current station ID
-- the one for next station
SELECT MIN(ID) as next FROM `stations` WHERE Cat_ID = {desidered_Cat_ID} AND ID > {your_current_ID} LIMIT 1;
-- the one for previous station
SELECT MAX(ID) as prev FROM `stations` WHERE Cat_ID = {desidered_Cat_ID} AND ID < {your_current_ID} LIMIT 1;
Hoping this helps.
I am not sure what you mean by "next", how is order determined. At any rate, this is the basic approach:
select t2.ID
from MyTable t1
inner join MyTable t2 on t1.Cat_ID = t2.Cat_ID and t1.ID <> t2.ID
select t2.id
from mytable t1
inner join mytable t2 on t2.cat_id = t1.cat_id and t2.id > t1.id
limit 1
Somewhat unclear on as others too.. Is the 1 the ID you want to find the next ID by the same category as ID 1 (thus ID 4 is also same Cat_ID 1)? I would try
select MIN( S2.ID ) as NextID
from
Stations S1
JOIN Stations S2
on S1.Cat_ID = S2.Cat_ID
AND S2.ID > S1.ID
where
S1.ID = IDYouAreInterestedIn
I've renamed the table and aliases to match your new table edits... In this case, "S1" and "S2" are "aliases" to your "Stations" table.
The Join command is based on the same category between the tables, but in addition, only include records in the second instance that have an ID GREATER than the one you are starting with (in your case, you were looking for 1).. You don't want to return 1 as your answer for the 1 record you are starting with. You want the first number AFTER the one you are on.
So, from your latest sample...
Stations table
------------------
ID Sta_Name Cat_ID
1 sta1 1
2 sta2 3
3 sta3 2
4 sta4 1
5 sta5 1
6 sta6 3
IF you enter The next ID would be
ID = 1 4 (both category 1)
ID = 2 6 (the next instance of category 3)
ID = 3 No record found... no other "2" category
ID = 4 5 (first ID after 4 but same "1" category)
ID = 6 No record since nothing after 6 in the list.
精彩评论