SQL Select unique values in 1 column

Question

I've got problem (a little problem I suppose) and I hope, you'll help me. I use Sybase Anywhere and here's my code:

SELECT TOP 4 Person.Id_person, Person.Name, Person.Surname, Visit.Date, Visit.Place
From Person, Visit
WHERE Visit.Id_person = Person.Id_person
ORDER BY Visit.DATE DESC

and here's the result:

3  | Paul | McDonald | 2010-01-19 | Ohio
3  | Paul | McDonald | 2010-01-18 | New York
19 | Ted  | Malicky  | 2009-12-24 | Tokyo
12 | Meg  | Newton   | 2009-10-13 | Warsaw

and I would like not to duplicate Paul McDonald, and have only first (by the date) visit. I'd like to have result like this:

3  | Paul | McDonald | 2010-01-19 | Ohio
19 | Ted  | Malicky  | 2009-12-24 | Tokyo
12 | Meg  | Newton   | 2009-10-13 | Warsaw
 ....

What should I do? Could you help me? :(

Answer 1

Here's a different way to do it using the ROW_NUMBER function to ensure that if someone has two meetings on the same day it still works:

SELECT TOP 4
    Person.Id_person,
    Person.Name,
    Person.Surname,
    T1.Date,
    T1.Place
FROM
    (SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY Id_person ORDER BY Date DESC) AS rn
    FROM Visit) AS T1
JOIN Person
ON T1.Id_person = Person.Id_person
WHERE rn = 1
ORDER BY Date DESC

Here's the result I get:

Id_person Name Surname  Date       Place
3         Paul McDonald 2010-01-19 Ohio
19        Ted  Malicky  2009-12-24 Tokyo
12        Meg  Newton   2009-10-13 Warsaw
1         Foo  Bar      2009-06-03 Someplace

Here's the test data I used:

CREATE TABLE Person (Id_person INT NOT NULL, Name NVARCHAR(100) NOT NULL, Surname NVARCHAR(100) NOT NULL);
INSERT INTO Person (Id_person, Name, Surname) VALUES
(3, 'Paul', 'McDonald'),
(19, 'Ted', 'Malicky'),
(12, 'Meg', 'Newton'),
(1, 'Foo', 'Bar'),
(2, 'Baz', 'Qux');

CREATE TABLE Visit (Id_person INT NOT NULL, Date DATE NOT NULL, Place NVARCHAR(100) NOT NULL);
INSERT INTO Visit (Id_person, Date, Place) VALUES
(3, '2010-01-19', 'Ohio'),
(3, '2010-01-18', 'New York'),
(19, '2009-12-24', 'Tokyo'),
(12, '2009-10-13', 'Warsaw'),
(1, '2009-06-03', 'Someplace'),
(12, '2009-10-13', 'Anotherplace'),
(2, '2009-05-04', 'Somewhere');

Tested on SQL Server 2008, but I believe the syntax for Sybase is similar.