SQL Query a Table grouped by some columns but taking the first row ordered by another column

Question

With a summary table below

CardNumber	Name	LastName	LastEntrance
123	Name1	Lastname1	2021-12-01 18:00:00
123	Name2	Lastname2	2021-12-01 17:00:00
234	Name3	Lastname3	2021-12-01 10:00:00
234	Name5	Lastname5	2021-12-01 09:00:00
567	Name4	Lastname4	2021-12-01 16:00:00

I want to have a table with unique CardNumber, Name, LastName grouped rows with the most recent LastEntrance of. My result table should be:

CardNumber	Name	LastName	LastEntrance
123	Name1	Lastname1	2021-12-01 18:00:00
234	Name3	Lastname3	2021-12-01 10:00:00
567	Name4	Lastname4	2021-12-01 16:00:00

Could I query this table with a simple SQL query?

[Check out this post](https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) — Chris Albert, Dec 02 '21 at 18:13
To keep the question simple I didn't share the whole thing. As soon as I try the proposed solutions, I'll return and update the question. Thanks for all answers. — serkanz, Dec 02 '21 at 19:12
@serkanz if the solution doesn't apply at 100%, I will suggest you make a new question, first because we try to make QA very specific for further use for others users, but second because you will get more attention than editing it with already 3 answers. — Leandro Bardelli, Dec 02 '21 at 19:42

score 1 · Accepted Answer · answered Dec 02 '21 at 18:10

I like the logic of crossing with itself and compare every row with the another one if it's bigger. Then only show the crossed ones. No subqueries, more performance, more easy to read.

SELECT t1.*
FROM mytable t1
  LEFT OUTER JOIN mytable t2
    ON (t1.CardNumber = t2.CardNumber 
         AND t1.Name = t2.Name          
         AND t1.LastName = t2.LastName
         AND t1.LastEntrance < t2.LastEntrance)
WHERE t2.CardNumber IS NULL;

score 1 · Answer 2 · edited Dec 02 '21 at 18:33

1

Use a subquery to find the max(LastEntrance) by employee to get the desired results

select * 
from cards cards1
where LastEntrance = (select Max(LastEntrance) 
                      from cards cards2
                      where cards1.CardNumber = cards2.CardNumber)

edited Dec 02 '21 at 18:33

marc_s

732,580
175
1,330
1,459

answered Dec 02 '21 at 18:10

Golden Lion

3,840
2
26
35

score 1 · Answer 3 · answered Dec 02 '21 at 18:25

You don't need to group by at all. since you just want to most recent you can do as below. By querying most recent LastEntrance from your table and using that in your main query.

Select t.CardNumber,t.Name,t.LastName,t.LastEntrance from tableX t
where t.LastEntrance= (select Max(a.LastEntrance) from tableX a where a.CardNumber=t.CardNumber)
order by t.CardNumber

SQL Query a Table grouped by some columns but taking the first row ordered by another column

3 Answers3