SQL Server Group by Query Select First Row Each Group

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 Server Group By Query Select first row each group

You can GROUP BY StudyID, Year and then in an outer query select the first row from each StudyID, Year group:

SELECT StudyID, Year, minAccess1, minAccess2, minAccess3
FROM (
   SELECT StudyID, Year, min(Access1) minAccess1, min(Access2) minAccess2,
          min(Access3) minAccess3, 
          ROW_NUMBER() OVER (PARTITION BY StudyID ORDER BY Year DESC) AS rn 
   FROM mytable
   GROUP BY StudyID, Year ) t
WHERE t.rn = 1

ROW_NUMBER is used to assign an ordering number to each StudyID group according to Year values. The row with the maximum Year value is assigned a rn = 1.

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.

sql server select first row from a group

If as you indicated, order doesn't matter, any aggregate function on b would be sufficient.

Example Using MIN

SELECT a, b = MIN(b)
FROM   YourTable
GROUP BY
       a

get first row fo each group SQL

If you are runing MySQL 8.0, you can use RANK() in a subquery to rank the records by subject count for each gender, and filter on the top record per group in the outer query (if there are top ties, RANK() preserves them):

SELECT gender, subject, no_of_selections
FROM (
    SELECT  
        se.gender, 
        su.subject, 
        COUNT(*) as no_of_selections,
        RANK() OVER(PARTITION BY se.gender ORDER BY COUNT(*) DESC) rn
    FROM selection se
    JOIN subjects su ON se.subjectID  = su.subjectID
    GROUP BY se.subjectID, se.gender, su.subject
) t
WHERE rn = 1
ORDER BY gender DESC

In earlier versions, where window functions are not availabe, one option is to filter with a having clause that returns to top count per gender:

SELECT  
    se.gender, 
    su.subject, 
    COUNT(*) as no_of_selections
FROM selection se
JOIN subjects su ON se.subjectID  = su.subjectID
GROUP BY se.subjectID, se.gender, su.subject
HAVING COUNT(*) = (
    SELECT COUNT(*) 
    FROM selection se1
    WHERE se1.gender = se.gender
    GROUP BY se1.subjectID, se1.gender
    ORDER BY COUNT(*) DESC
    LIMIT 1
)

Notes:

• I changed the table aliases to make them more meaningful

• You should the subject column to the GROUP BY clause to make your query runnable under sql mode ONLY_FULL_GROUP_MODE, which is by default enabled starting MySQL 5.7

Selecting first row per group

SELECT  a, b, c
FROM    (
        SELECT  *, ROW_NUMBER() OVER (PARTITION BY a ORDER BY b, c) rn
        FROM    mytable
        ) q
WHERE   rn = 1
ORDER BY
        a

or

SELECT  mi.*
FROM    (
        SELECT  DISTINCT  a
        FROM    mytable
        ) md
CROSS APPLY
        (
        SELECT  TOP 1 *
        FROM    mytable mi
        WHERE   mi.a = md.a
        ORDER BY
                b, c
        ) mi
ORDER BY
        a

Create a composite index on (a, b, c) for the queries to work faster.

Which one is more efficient depends on your data distribution.

If you have few distinct values of a but lots of records within each a, the second query would be better.

You could improve it even more by creating an indexed view:

CREATE VIEW v_mytable_da
WITH   SCHEMABINDING
AS
       SELECT  a, COUNT_BIG(*) cnt
       FROM    dbo.mytable
       GROUP BY
               a

GO

CREATE UNIQUE CLUSTERED INDEX
       pk_vmytableda_a
ON     v_mytable_da (a)

GO

SELECT  mi.*
FROM    v_mytable_da md
CROSS APPLY
        (
        SELECT  TOP 1 *
        FROM    mytable mi
        WHERE   mi.a = md.a
        ORDER BY
                b, c
        ) mi
ORDER BY
        a

How do I select the first row per group in an SQL Query?

declare @sometable table ( foo int, bar int, value int )

insert into @sometable values (47, 1, 100)
insert into @sometable values (47, 0, 10)
insert into @sometable values (47, 2, 10)
insert into @sometable values (46, 0, 100)
insert into @sometable values (46, 1, 10)
insert into @sometable values (46, 2, 10)
insert into @sometable values (44, 0, 2)

WITH cte AS 
(
    SELECT   Foo, Bar, SUM(value) AS SumValue, ROW_NUMBER() OVER(PARTITION BY Foo ORDER BY FOO DESC, SUM(value) DESC) AS RowNumber
    FROM     @SomeTable
    GROUP BY Foo, Bar
)
SELECT * 
FROM cte
WHERE RowNumber = 1

Select the first row for each group in MySQL?

You can GROUP BY and pick the MAX position.

SELECT ri.*
FROM (
    SELECT ri.release_id, MAX(ri.position) AS position
    FROM release_image ri
    GROUP BY ri.release_id
) ri_max
INNER JOIN release_image ri ON ri_max.release_id = ri.release_id
    AND ri_max.position = ri.position

BigQuery/SQL: Select first row of each group

I believe you are looking for the function [FIRST_VALUE][1]?

SELECT 
    landing_page,   
    FIRST_VALUE(URL)  
        OVER ( PARTITION BY landing_page ORDER BY Page_Type DESC) AS first_url
FROM `xxxx.TEST.draft`

How to get the first row per group?

if your MySQL version support ROW_NUMBER + window function, you can try to use ROW_NUMBER to get the biggest num by category_id

Query #1

SELECT num,business_id,category_id
FROM (
    SELECT *,ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY num desc) rn
    FROM (
        select count(1) num, business_id, category_id
        from mytable
        group by business_id, category_id
    ) t1
) t1
WHERE rn = 1

---