...

View Full Version : Selecting on the first row..



c4p0ne
01-27-2007, 10:14 AM
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"

________________________________
| 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.

guelphdad
01-27-2007, 03:40 PM
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()

c4p0ne
01-27-2007, 07:56 PM
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;

guelphdad
01-28-2007, 01:49 AM
yes you could do it like that. Surprising you just didn't modify your first query above though.


SELECT
min(softkey)
FROM gamenew
where
inuse = 0

guelphdad
01-28-2007, 01:51 AM
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 (http://sqlcourse.com/) where you can try stuff with an online interface.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum