PostgreSQL

Скачать в pdf «PostgreSQL»


For example, suppose you want to find the name of your oldest friend in each state. You can accomplish this task with having and table aliases, as shown in the first query of Figure 8.9. Alternatively, you can execute a subquery for each row that finds the maximum age for that state. If the maximum age equals the age of the current row, the row is output, as shown in the second query. This query references the friend table two times, using the aliases fl and f2. The upper query uses fl. The subquery uses f2. The correlating specification is WHERE fl.state = f2.state,


test=> SELECT * FROM friend ORDER BY firstname;


firstname |    lastname    |    city    | state | age


——+—————-


——+—————


—+——


—+-


Dean


| Yeager


| Plymouth


| MA


|


24


Dick


| Gleason


| Ocean City


| nj


|


19


Ned


| Millstone


| Cedar Creek


| MD


|


27


Sandy


| Gleason


| Ocean City


| nj


|


25


Sandy


| Weber


| Boston


| MA


|


33


Victor


| Tabor


| Williamsport


| PA


|


22

(6 rows)


test=> SELECT fl.firstname, fl.lastname, fl.state


test-> FROM friend fl, friend f2


test-> WHERE fl.state <> f2.state AND


test->    f2.firstname = ‘Dick’ AND


test->    f2.lastname= ‘Gleason’

Скачать в pdf «PostgreSQL»