Selecting and Arranging Rows 

You can select and arrange specific rows in the query.

Prerequisites

You require the demo data for the SQL Tutorial.

Start the query tool SQL Studio as database administrator MONA with password RED and log on to the demo database instance DEMODB.

Selecting Specific Rows

To select specific rows, use the WHERE clause.

 

SELECT zip, name
  FROM hotel.city
    WHERE name = 'Seattle'

Selecting the rows with the city name Seattle

Result

ZIP	NAME
20005	Seattle
20019	Seattle
20037	Seattle

See also:

SQL Reference Manual, WHERE Clause

 

SELECT ROWNO, cno, title, firstname, name
  FROM hotel.customer
    WHERE ROWNO <= 5

Selecting the first five rows and numbering the rows

Result

ROWNO	CNO	TITLE	FIRSTNAME	NAME
1	3000	Mrs	Jenny	Porter
2	3100	Mr	Peter	Brown
3	3200	Company	?	Datasoft
4	3300	Mrs	Rose	Brian
5	3400	Mrs	Mary	Griffith

See also:

SQL Reference Manual, ROWNO Predicate (rowno_predicate)

 

Arranging the Rows

To arrange the rows, use the ORDER clause that specifies the order in which the rows are to appear.

 

SELECT name, firstname
  FROM hotel.customer
    WHERE ROWNO <= 5
    ORDER BY name

Sorting the first five rows in alphabetical order by customer name

Result

NAME	FIRSTNAME
Brian	Rose
Brown	Peter
Datasoft	?
Griffith	Mary
Porter	Jenny

 

SELECT name, firstname
  FROM hotel.customer
    WHERE ROWNO <= 5
    ORDER BY name DESC

Sorting the first five rows alphabetically by customer name, in descending order

Result

NAME	FIRSTNAME
Porter	Jenny
Griffith	Mary
Datasoft	?
Brown	Peter
Brian	Rose

Instead of the sort column name, you can also specify the position number in the output list (that is, ORDER BY 1 DESC instead of ORDER BY name DESC).

Unless otherwise stated, sort columns are always sorted in ascending order. A sort column does not necessarily also have to be an output column.

 

See also:

SQL Reference Manual, ORDER Clause (order_clause)

 

Arranging Rows by More Than One Column

If you want to stagger the sort sequence, you can specify the sort column names in order of importance; each name can be assigned the add-on ASC or DESC, or both.

 

SELECT zip, name
  FROM hotel.city
    WHERE ROWNO < 5
    ORDER BY name, zip DESC

Displaying the city/place data, sorted by name and in descending order by zip code

Result

ZIP	NAME
12203	Albany
11788	Long Island
10580	New York
10019	New York

 

Displaying Tables Without Duplicate Rows

You can prevent redundant information from being displayed in the query result by specifying the keyword DISTINCT after the keyword SELECT.

 

SELECT DISTINCT name, state
  FROM hotel.city
    ORDER BY name

Displaying the place/city data consisting of a different name and state

Result

NAME	STATE
Albany	NY
Chicago	IL
Cincinnati	OH
Clearwater	FL
Dallas	TX
Daytona Beach	FL
Deerfield Beach	FL
Detroit	MI
Hollywood	CA
Irvine	CA
Long Beach	CA
Long Island	NY
Los Angeles	CA
New York	NY
Palm Spring	CA
Portland	OR
Rosemont	IL
Santa Clara	CA
Seattle	WA
Silver Spring	MD

For comparison purposes, execute the SQL statement without specifying the keyword DISTINCT.

See also:

SQL Reference Manual, DISTINCT Specification (distinct_spec)

 

See also:

SQL  Reference Manual, QUERY Expression (query_expression), Selected Column (select_column), Table Expression (table_expression)

More examples for Data Query