Background documentationSelecting and Arranging Rows Locate this document in the navigation structure

 

You can select and arrange specific rows in the query.

Prerequisites

You can use the demo data for the SQL tutorial. Start the Database Studio as database administrator MONA with the password RED and log on to demo database DEMODB: Logging On to a Database.

Activities

You can use Database Studio to enter and execute SQL statements. More information: Working with SQL Statements: Overview

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. Specify 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)