-4

Does anyone know how to take always the first row after group by in SQL Server? Look on the screenshot for better explanation:

Result after select:
+------+---------+------+
| NAME | CAR     | AGE  |
+------+---------+------+
| Alex | BMW     | 5    |
+------+---------+------+
| Alex | Audi    | 2    |
+------+---------+------+
| Tom  | VW      | 10   |
+------+---------+------+
| Tom  | Renault | 4    |
+------+---------+------+
| Tom  | Peugeot | 2    |
+------+---------+------+

Expected result after group by:
+------+-----+
| NAME | CAR |
+------+-----+
| Alex | BMW |
+------+-----+
| Tom  | VW  |
+------+-----+
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
user1966102
  • 1
  • 2
  • 3
    [Please do not upload images of code/data/errors when asking a question.](//meta.stackoverflow.com/q/285551) – Thom A Jun 24 '22 at 14:44
  • 2
    Please, refer to: https://stackoverflow.com/questions/7344731/sql-server-select-first-row-from-a-group – 0xD Jun 24 '22 at 14:58
  • Thank you guys for your comments, but I have to say it is pain to create a table only with chars. But now I have it :-). – user1966102 Jun 24 '22 at 18:49
  • There is no such thing as a first row when an ordering hasn't been applied. – shawnt00 Jun 24 '22 at 19:01

2 Answers2

1

You can try to use the ROW_NUMBER() window function with PARTITION_BY clause. This function assigns a sequential integer to each row within the partition of a result set. The row number starts with 1 for the first row in each partition.
After that, you can use the where clause to select rows that have row numbers as 1.

You can follow this article for a better understanding.

Below is just an example (As I don't know how your query works):

select *
from
(
    SELECT 
        ROW_NUMBER() OVER (PARTITION BY name ORDER BY name) row_num, 
        *
       from(
         -- your main group by query
     )
)
where row_num=1
Sayan Bhattacharya
  • 1,365
  • 1
  • 4
  • 14
0

you should be able to get the top 1 record for each person using the below query, let me know if this works for you. SELECT * FROM car_owners GROUP BY person_name;

let me know if you want to order the records in alphabetical order ASC or DESC and then GROUP BY them

Thank You enter image description here

Purke Gopinand
  • 9
  • 5
  • :-) Thank you for the image, but do I have a guaranty that GROUP BY takes always the first row? That is the reason why I am asking. What is if my table has three columns an I want to have two columns as a result? I modified my table in my main question. – user1966102 Jun 24 '22 at 18:55
  • Yes, it can get the first record (car) of every person, and guarantee only depending on the order of the records stored in db. – Purke Gopinand Jun 24 '22 at 19:03
  • Thank you, thats the answer what I needed! – user1966102 Jun 24 '22 at 20:10
  • Thank You, happy helping you @user1966102, please upvote this answer, to help others on StackOverflow – Purke Gopinand Jun 25 '22 at 06:28