SELECT, WHERE, ORDER BY: Writing Your First Real SQL Server Queries

This is the query you’ll write more than any other, in some form, for the rest of your career. Getting a genuinely solid mental model here — not just “it works” but why it works in the order it does — pays off in every later chapter.

Setup — run this once to follow along:

CREATE TABLE dbo.Restaurant (
    restaurant_id INT IDENTITY(1,1) PRIMARY KEY,
    name          NVARCHAR(100) NOT NULL,
    cuisine       NVARCHAR(50)  NOT NULL,
    city          NVARCHAR(50)  NOT NULL,
    rating        DECIMAL(2,1)  NOT NULL,
    price_range   TINYINT       NOT NULL
);
INSERT INTO dbo.Restaurant (name, cuisine, city, rating, price_range) VALUES
('Spice Route', 'Indian', 'Austin', 4.5, 2),
('Nonna''s Table', 'Italian', 'Austin', 4.7, 3),
('Sakura Grill', 'Japanese', 'Austin', 4.8, 3),
('Taco Libre', 'Mexican', 'Dallas', 4.3, 1);

SELECT: Choose Your Columns

SELECT name, cuisine, rating FROM dbo.Restaurant;

-- SELECT * grabs every column — fine for exploring, avoid it in real application code
SELECT * FROM dbo.Restaurant;

-- Aliasing a column for a cleaner result header
SELECT name AS restaurant_name, rating AS star_rating FROM dbo.Restaurant;

WHERE: Filter the Rows

SELECT name, rating FROM dbo.Restaurant WHERE city = 'Austin';

WHERE is evaluated once per row, against the raw table data — it runs before SELECT decides which columns to keep, which is why you can filter on a column you don’t even include in the output.

ORDER BY, DISTINCT, and TOP

SELECT name, rating FROM dbo.Restaurant ORDER BY rating DESC;
SELECT DISTINCT cuisine FROM dbo.Restaurant;
SELECT TOP 3 name, rating FROM dbo.Restaurant ORDER BY rating DESC;
SELECT TOP 25 PERCENT name FROM dbo.Restaurant ORDER BY rating DESC;

TOP without an ORDER BY gives you an arbitrary N rows in whatever order the engine happens to read them — not “the first N you inserted,” and not stable across runs. Always pair TOP with ORDER BY when you mean “the highest/lowest N,” which is almost always what you actually want.

The Order SQL Server Actually Processes Your Query

Even though you type SELECT first, SQL Server processes FROM → WHERE → SELECT → ORDER BY (this is called logical query processing order, and it’s the single most useful mental model for debugging “why doesn’t this work” moments for the rest of this course). That’s why a column alias defined in SELECT can’t be reused in that same query’s WHERE clause — WHERE runs before SELECT even exists:

-- This fails:
SELECT rating * 2 AS double_rating FROM dbo.Restaurant WHERE double_rating > 8;
-- Msg 207: Invalid column name 'double_rating'

-- Because WHERE runs before the alias exists, repeat the expression instead:
SELECT rating * 2 AS double_rating FROM dbo.Restaurant WHERE rating * 2 > 8;

ORDER BY, on the other hand, runs last — after SELECT — which is exactly why it’s the one clause that can reference a column alias.

Comments and Readability

-- single-line comment
/* multi-line
   comment block */
SELECT name, rating -- inline comment on the same line
FROM dbo.Restaurant
WHERE city = 'Austin'; -- Austin locations only

Key Takeaways

• SELECT picks columns; WHERE filters rows; ORDER BY sorts the result
• DISTINCT removes duplicate rows; TOP limits row count — always pair TOP with ORDER BY, or “top” is meaningless
• Logical execution order (FROM → WHERE → SELECT → ORDER BY) explains several “why doesn’t this work” surprises, including why WHERE can’t see a SELECT alias but ORDER BY can
• Avoid SELECT * outside of quick exploration — name your columns explicitly