Timeline for Find "n" consecutive free numbers from table
Current License: CC BY-SA 3.0
13 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Mar 19, 2013 at 8:59 | comment | added | boobiq | this looks fine without window functions, but it returns all the "last numbers" for all possibilities (sqlfiddle.com/#!12/13127/10), so for n=3 it returns just 000010 (i wanted to return all free numbers for this group, so it would be 000008, 000009, 000010.. and i'd like to have only first possible group) | |
| Mar 18, 2013 at 19:04 | comment | added | Erwin Brandstetter |
I took the liberty to fix the syntax for Postgres. the first EXISTS could be simplified. Since we only need to make sure any n earlier rows exist, we can drop the AND status = 'FREE'. And I would change the condition in the 2nd EXISTS to status <> 'FREE' to harden it against added options in the future.
|
|
| Mar 18, 2013 at 19:03 | history | edited | Erwin Brandstetter | CC BY-SA 3.0 |
fix postgres syntax
|
| Mar 18, 2013 at 18:23 | comment | added | boobiq | Yes, the numbers in one set are unique and consective from 000 to 999 without any gaps, I don't have time to try this, I will try it tomorrow, thanks very much to all of you for now :) | |
| Mar 18, 2013 at 18:15 | comment | added | Andriy M | I can see no problem with that. The appearance of the data sample very clearly suggests that the numbers are unique and consecutive. If they aren't what they seem then it's the OP's responsibility to make sure we are not deceived by the looks of things, even though it is never a bad idea to spell out your assumptions (which you did). So, I will still say this is a nice solution for a specific design. | |
| Mar 18, 2013 at 18:05 | history | edited | JNK | CC BY-SA 3.0 |
added 4 characters in body
|
| Mar 18, 2013 at 18:04 | comment | added | JNK |
@AndriyM Well it's not "nice" it's fragile since it relies on sequential values of that number field. Good call on the math I'll correct it.
|
|
| Mar 18, 2013 at 18:01 | comment | added | Andriy M |
No window functions, very nice! Although I think it should be M.number-consec+1 (e.g. for 10 it would need to be 10-3+1=8).
|
|
| Mar 18, 2013 at 17:56 | history | rollback | swasheck |
Rollback to Revision 1
|
|
| Mar 18, 2013 at 17:37 | history | edited | swasheck | CC BY-SA 3.0 |
edited body
|
| Mar 18, 2013 at 17:24 | comment | added | JNK | @a_horse_with_no_name Please feel free to fix that then :) | |
| Mar 18, 2013 at 17:23 | comment | added | user1822 | The declare won't work like that in Postgres. | |
| Mar 18, 2013 at 17:16 | history | answered | JNK | CC BY-SA 3.0 |