CODEWITHSUNDEEP
sqlsql-server
Andrey Parfyonov
Andrey Parfyonov

Reputation: 13

How to show only one row with max date?

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? picture

Upvotes: 1

Views: 121

Answers (4)

gofr1
gofr1

Reputation: 15977

You don't need CTE, just use TOP WITH TIES + ROW_NUMBER()

SELECT TOP 1 WITH TIES *
FROM guests
ORDER BY ROW_NUMBER() OVER(PARTITION BY lastname, firstname ORDER BY dateto DESC)

Upvotes: 0

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

try this

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

Upvotes: 0

Michał Turczyn
Michał Turczyn

Reputation: 37367

I think this the simpliest solution...:

select top 1 * from MY_VIEW
where lastname = 'Collins' and firstname = 'Frank'
order by dateto desc

or more edible:

select top 1 * from MY_VIEW
where lastname + ' ' + firstname = 'Collins Frank'
order by dateto desc

Upvotes: 2

Vash
Vash

Reputation: 1787

Try: 

SELECT
A.LASTNAME, A.FIRSTNAME, A.DATEFROM, A.DATETO, A.ROOMNUMBER, A.CITY, A.COUNTRY, A.STATUS, A.DAYS
FROM
(SELECT LASTNAME, FIRSTNAME, MAX(DATETO) AS MOST_RECENT_STAY
FROM YOUR_TABLE 
GROUP BY LASTNAME, FIRSTNAME) Z
INNER JOIN
YOUR_TABLE A
ON Z.LASTNAME = A.LASTNAME AND Z.FIRSTNAME = A.FIRSTNAME AND Z.MOST_RECENT_STAY = A.DATETO;

Upvotes: 0

Related Questions