How to get the top 10 values in PostgreSQL?

Banishing duplicates

Do you want a unique list? Add DISTINCT:

-- Be Unique. Be Distinct. But don't be indistinct. 
SELECT DISTINCT ON (your_column) * FROM your_table
ORDER BY your_column DESC
LIMIT 10;


DISTINCT ON leaves you with unique top values, but if you've got rows with the same value, you may need to sort them.

Dealing with ties and pagination

In situations where rank() matters, tackle ties with this window function:

-- Ties? We're not wearing ties here.
WITH RankedValues AS (
  SELECT *, rank() OVER (ORDER BY your_column DESC) as rnk 
  FROM your_table
)
SELECT * FROM RankedValues
WHERE rnk <= 10;


Plan to paginate? No worries! OFFSET can be your mate:

-- Pagination? No frustration, just use offset and pagination!
SELECT * FROM your_table
ORDER BY your_column DESC
LIMIT 10 OFFSET 10;


Boosting performance

Indexing your_column can significantly enhance query performance. But mind the trade-off with slower inserts and updates. Even SQL needs a balanced diet!

Surgical precision with field selection

Use SELECT wisely to improve performance:

-- Save trees, SELECT only needed fields!
SELECT your_column FROM your_table
ORDER BY your_column DESC
LIMIT 10;


Don't forget to test with SQL Fiddle and use EXPLAIN to understand your query's plan.

Reinforcing compatibility

For broader database compatibility, keep SQL:2008 standards in view. PostgreSQL 8.4+ presents the flavorful syntax fetch first:

-- SQL:2008 sends their regards!
SELECT * FROM your_table
ORDER BY your_column DESC
FETCH FIRST 10 ROWS ONLY;


Ideal for cross-platform SQL authors!

Tactics for large data-banks

Creating efficient queries for large datasets is an art:

1. Use subquery for efficient filtering with window functions.
2. Analyze and vacuum your tables to keep stats updated.
3. Partitioning can be your friend for humongous datasets.