Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New to the CF scene
    Join Date
    Jan 2007
    Location
    Classified
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Selecting on the first row..

    Hello, I would like to as a question but since I am awful at doing such things (asking questions) I will try to illustrate my question so that I don't wind up even confusing myself! It should be pretty straightforward and simple to do, I just can't figure out how as I'm new to ANY kind of SQLing.

    I have a table lets say "gamenew"
    Code:
    ________________________________
    | ID | SoftKey | Valid | InUse |
    ````````````````````````````````
    | 1  |  XX123  |   1   |   1   |
    ````````````````````````````````
    | 2  |  XX124  |   1   |   1   |
    ````````````````````````````````
    | 3  |  XX125  |   1   |   0   |
    ````````````````````````````````
    | 4  |  XX126  |   1   |   0   |
    ````````````````````````````````
    | 5  |  XX127  |   1   |   1   |
    ````````````````````````````````
    | 6  |  XX128  |   1   |   0   |
    ````````````````````````````````
    Ok so I would like to SELECT only the first "SoftKey" that is NOT in use (indicated by InUse = 0). So in other words for this specific example I would like a SELECT statement that would produce the following result:

    XX125

    Since XX125 is the first SoftKey in the table which is not being used. I'm not looking for multiple results like:

    XX125
    XX126
    XX128

    Thanks.
    Last edited by c4p0ne; 01-27-2007 at 06:57 PM. Reason: Missed a spot ;)

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    a table is not like a flat file. there is no order to a table until you impose an order by clause. thus you can't say first row. you can say softkey with the lowest value that is not in use.

    if that is what you want it is a straightforward query with the use of MIN()

  • #3
    New to the CF scene
    Join Date
    Jan 2007
    Location
    Classified
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thumbs up

    So I punch in SELECT min( InUse ) FROM gamenew; but of course it outputs a "0". I want it to output the first SoftKey it finds (or any SoftKey it finds) with it's InUse set to "0". Once again I literally have never used any databases ever before making this original first post so forgive me if these questions seem trivial or "non-topics" to those of you who didn't have to start from somewhere at some point..

    So it doesn't matter if I get XX125, XX126 or XX128 returned (so that I can stor it in a variable for subsiquent use) as long as its a SoftKey that isn't already being used.

    ::EDIT::

    BAM, I think i've got it! but more testing will be required.. What I wanted SEEMS to be getting done like this:

    SELECT softkey FROM gamenew WHERE InUse = 0 LIMIT 0, 1;
    Last edited by c4p0ne; 01-27-2007 at 07:33 PM. Reason: Starting to formulate an answer!

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    yes you could do it like that. Surprising you just didn't modify your first query above though.

    Code:
    SELECT 
    min(softkey) 
    FROM gamenew
    where 
    inuse = 0

  • #5
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    If you want good basics you can search on Kevin Yank. He has a book on basic SQL, there are three or four sample chapters available online. It is resonably current.

    you can also check out this online course where you can try stuff with an online interface.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •