Share
Go down
avatar
Admin
Posts : 155
Join date : 2017-11-11
Age : 27
View user profilehttp://net-media.yoo7.com

How to show only one row with max date?

on Wed Dec 13, 2017 11:45 am
I'm working on my database project right now. I made a view which shows info about guests. As you can see, there are two rows with Frank Collins. How can I show only one row with him (with recent date of his stay) using SELECT for the view?

Answers
Code:
WITH CTE
AS
(
    SELECT
        SeqNo = ROW_NUMBER() OVER(PARTITION BY LastName,FirstName ORDER BY DateTo DESC),
        *
        FROM Guests
)
SELECT
*
FROM CTE
    WHERE SeqNo = 1
This query will return the latest records for each user, if there are multiple records for a first name + last name combination, it will give you the latest one based on dateTo otherwise it will return the record as it is

So you will get only 1 row per guest each time you execute this
Back to top
Permissions in this forum:
You can reply to topics in this forum