CodingForums.com - View Single Post

Old Pedant · 02-01-2013, 06:00 AM

Ummm...the cell may not have a specific name, but the *FIELDS* (a.k.a. "columns", but that can be misleading, causing confusion with spreadsheets) MUST have names.

The 2nd cell is in the 2nd field. And the name of that field *IN ALL ROWS* is LastName.

So immediately you know to do

Code:

SELECT LastName FROM Persons

(assuming you are using that "Persons" table on that page as an example database table).

The harder part is figuring out how to select the correct *RECORD* (again, a.k.a. "row", but again too easily confused with spreadsheets).

The only way, in a database, to specify a single record is to pick a field (or combination of fields) that has a *UNIQUE* value that won't match any other records and use that uniqueness in a WHERE clause.

Now, in the example in that w3c schools page, each record has a unique P_id field, so you could certainly do

Code:

SELECT LastName FROM Persons WHERE P_id = 1

But here is the fun part: You could also do

Code:

SELECT LastName FROM Persons WHERE FirstName = 'Ola'

or
SELECT LastName FROM Persons WHERE Address = 'Timoteivn 10'
[/code]
because all 3 of those will pick out one and only one record, the first one listed in the table.

And here is the EVEN MORE fun part:

Code:

SELECT LastName FROM Persons WHERE LastName = 'Hansen'

But you could *NOT* do

Code:

SELECT LastName FROM Persons WHERE City ='Sandnes'

because that query will find *TWO* records, not just one.

But wait! You *could* do:

Code:

SELECT LastName FROM Persons WHERE City ='Sandnes'
            ORDER BY P_id LIMIT 1

The ORDER_BY ensures that you will find the records in the given order and the LIMIT 1 says "I only want the first record, in the given order, that matches."

There are many many many more ways to select particular single records and also groups of records. And that's the whole "fun" of using SQL.

02-01-2013, 06:00 AM	PM User \| #2
Old Pedant Supreme Master coder! Join Date: Feb 2009 Posts: 23,200 Thanks: 59 Thanked 3,996 Times in 3,965 Posts	Ummm...the cell may not have a specific name, but the FIELDS (a.k.a. "columns", but that can be misleading, causing confusion with spreadsheets) MUST have names. The 2nd cell is in the 2nd field. And the name of that field IN ALL ROWS is LastName. So immediately you know to do Code: SELECT LastName FROM Persons (assuming you are using that "Persons" table on that page as an example database table). The harder part is figuring out how to select the correct RECORD (again, a.k.a. "row", but again too easily confused with spreadsheets). The only way, in a database, to specify a single record is to pick a field (or combination of fields) that has a UNIQUE value that won't match any other records and use that uniqueness in a WHERE clause. Now, in the example in that w3c schools page, each record has a unique P_id field, so you could certainly do Code: SELECT LastName FROM Persons WHERE P_id = 1 But here is the fun part: You could also do Code: SELECT LastName FROM Persons WHERE FirstName = 'Ola' or SELECT LastName FROM Persons WHERE Address = 'Timoteivn 10' [/code] because all 3 of those will pick out one and only one record, the first one listed in the table. And here is the EVEN MORE fun part: Code: SELECT LastName FROM Persons WHERE LastName = 'Hansen' But you could NOT do Code: SELECT LastName FROM Persons WHERE City ='Sandnes' because that query will find TWO records, not just one. But wait! You could do: Code: SELECT LastName FROM Persons WHERE City ='Sandnes' ORDER BY P_id LIMIT 1 The ORDER_BY ensures that you will find the records in the given order and the LIMIT 1 says "I only want the first record, in the given order, that matches." There are many many many more ways to select particular single records and also groups of records. And that's the whole "fun" of using SQL. __________________ An optimist sees the glass as half full. A pessimist sees the glass as half empty. A realist drinks it no matter how much there is.