SQL Select unique values in 1 column

Question

Hello,

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

You can add a where not exists clause to filter out earlier visits:

SELECT TOP 4 p1.Id_person, p1.Name, p1.Surname, v1.Date, v1.Place
FROM Person p1, Visit v1
WHERE p1.Id_person = v1.Id_person
AND NOT EXISTS (
    SELECT *
    From Person p2, Visit v2
    WHERE v2.Id_person = p2.Id_person
    AND p1.Id_person = p2.Id_person
    AND v2.Date > v1.Date
)
ORDER BY v1.DATE DESC

To improve readability, consider rewriting the double from as a join. For example, change:

FROM Person v1, Visit v1
WHERE v1.Id_person = p1.Id_person

into:

FROM Person p1
INNER JOIN Visit v1 ON v1.Id_person = p1.Id_person

Answer 2

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.

Answer 3

There is an easier way and it'll show you the most recent trip for each person as well:

SELECT TOP 4 Person.Id_person, Person.Name, Person.Surname, Visit.Date, Visit.Place 
  From Person, Visit 
  WHERE Visit.Id_person = Person.Id_person 
      AND (Visit.[Date] = (Select Max([Date]) 
                            From Visit Where (Person.Id_person=Visit.Id_Person)))
  ORDER BY Visit.DATE DESC

I use a variant of this quite often in my work. The only caveat is that the "Date" field in the visit table is a DateTime (and, of course, that someone can't be in two places at the same time).