Geek Answers Handbook

SQL Select unique values ​​in 1 column

I have a problem (a small problem, I suppose), and I hope you can help me. I am using Sybase Anywhere and here is 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 is 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 the first (by date) visit. I would like to get this result:

 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?: (

+6
sql
source share
3 answers

Here's another way to do this with the ROW_NUMBER function, to make sure 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 is the result:

 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 we used test data:

 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 find the syntax for Sybase is similar.

+4
source share

There is an easier way and it will show you the last trip for each person:

 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, someone cannot be in two places at the same time).

+3
source share

You can add a where not exists clause to filter previous 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

in

 FROM Person p1 INNER JOIN Visit v1 ON v1.Id_person = p1.Id_person
+1
source share

More articles:


All Articles