Select the First 3 Rows of Each Table in a Database

Select the first 3 rows of each table in a database

Here you have:

DECLARE @sql VARCHAR(MAX)='';
SELECT @sql=@sql+'SELECT TOP 3 * FROM '+'['+SCHEMA_NAME(schema_id)+'].['+name+']'+';'
  FROM sys.tables
EXEC(@sql)

MySQL get every first 3 rows for every two other columns

For versions of MySQL pre-8.0...

SELECT x.*
  FROM my_table x 
  JOIN my_table y 
    ON y.a = x.a 
   AND y.b = x.b 
   AND y.factor >= x.factor 
 GROUP 
    BY x.a
     , x.b
     , x.factor 
HAVING COUNT(*) <= 3 
 ORDER 
    BY a
     , b
     , factor DESC;

...and for next time, please see: Why should I provide an MCRE for what seems to me to be a very simple SQL query?

Get top 1 row of each group

;WITH cte AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC) AS rn
   FROM DocumentStatusLogs
)
SELECT *
FROM cte
WHERE rn = 1

If you expect 2 entries per day, then this will arbitrarily pick one. To get both entries for a day, use DENSE_RANK instead

As for normalised or not, it depends if you want to:

• maintain status in 2 places
• preserve status history
• ...

As it stands, you preserve status history. If you want latest status in the parent table too (which is denormalisation) you'd need a trigger to maintain "status" in the parent. or drop this status history table.

Select first row in each GROUP BY group?

On databases that support CTE and windowing functions:

WITH summary AS (
    SELECT p.id, 
           p.customer, 
           p.total, 
           ROW_NUMBER() OVER(PARTITION BY p.customer 
                                 ORDER BY p.total DESC) AS rank
      FROM PURCHASES p)
 SELECT *
   FROM summary
 WHERE rank = 1

Supported by any database:

But you need to add logic to break ties:

  SELECT MIN(x.id),  -- change to MAX if you want the highest
         x.customer, 
         x.total
    FROM PURCHASES x
    JOIN (SELECT p.customer,
                 MAX(total) AS max_total
            FROM PURCHASES p
        GROUP BY p.customer) y ON y.customer = x.customer
                              AND y.max_total = x.total
GROUP BY x.customer, x.total

SQL - Select first 10 rows only?

In SQL server, use:

select top 10 ...

e.g.

select top 100 * from myTable
select top 100 colA, colB from myTable

In MySQL, use:

select ... order by num desc limit 10

How to select the nth row in a SQL database table?

There are ways of doing this in optional parts of the standard, but a lot of databases support their own way of doing it.

A really good site that talks about this and other things is http://troels.arvin.dk/db/rdbms/#select-limit.

Basically, PostgreSQL and MySQL supports the non-standard:

SELECT...
LIMIT y OFFSET x 

Oracle, DB2 and MSSQL supports the standard windowing functions:

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

(which I just copied from the site linked above since I never use those DBs)

Update: As of PostgreSQL 8.4 the standard windowing functions are supported, so expect the second example to work for PostgreSQL as well.

Update: SQLite added window functions support in version 3.25.0 on 2018-09-15 so both forms also work in SQLite.

How to select the first row for each group in MySQL?

rtribaldos mentioned that in younger database versions, window-functions could be used.

Here is a code which worked for me and was as fast as Martin Zwarík's substring_index-solution (in Mariadb 10.5.16):

SELECT group_col, order_col FROM (
  SELECT group_col, order_col
  , ROW_NUMBER() OVER(PARTITION BY group_col ORDER BY order_col) rnr 
  FROM some_table
  WHERE <some_condition>
) i
WHERE rnr=1;

---

Related Topics