How to show only one line per id 1

[Thiesen]

I have a table with three colunms PERSON_ID, MONTH, YEAR.

[u]PERSON_ID    MONTH    YEAR[/u]
1071403          5    1991
1071403          6    1991
1071403          7    1991
1071403          1    1992
1183071          3    1991
1183071          5    1991
1183071          9    1991
1183071          3    1992
1097890          9    1995
1097890          4    1995

I need to show only the first MONTH and YEAR of all persons.

espected result:

PERSON_ID    MONTH    YEAR
1071403          5    1991
1183071          3    1991
1097890          4    1995

I try with TOP 1 , DISTINCT and nothing...

 

[ptheriault]

I think this might work.

DECLARE @table table (ID INT, MONTH int, year int)

INSERT INTO @table VALUES(1071403, 5, 1991)
INSERT INTO @table VALUES(1071403, 6, 1991)
INSERT INTO @table VALUES(1071403, 7, 1991)
INSERT INTO @table VALUES(1071403, 1, 1992)
INSERT INTO @table VALUES(1071403, 1, 1992)
INSERT INTO @table VALUES(1071401, 1, 1991)
INSERT INTO @table VALUES(1071401, 2, 1992)


SELECT DISTINCT a.ID
      ,b.Month
      ,c.YEAR
FROM @table a
JOIN
(SELECT ID
      ,MIN(MONTH) as Month
FROM @table
GROUP BY ID) as b
ON a.ID = b.ID
JOIN
(SELECT ID
      ,MIN(YEAR) as YEAR
FROM @table
GROUP BY ID) as c
ON a.ID = c.ID

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[gmmastros]

Paul,

Try again. What you really need is the Min Month and Year combination. When I run your query against the sample data, for ID = 1071403, I get month=1, year=1991. No such record exists.

Anyway, let's have a little fun with this one, shall we. I came up with a solution that does not require any subqueries. To help get you started, consider this...

DECLARE @table table (ID INT, MONTH int, year int)

INSERT INTO @table VALUES(1071403, 5, 1991)
INSERT INTO @table VALUES(1071403, 6, 1991)
INSERT INTO @table VALUES(1071403, 7, 1991)
INSERT INTO @table VALUES(1071403, 1, 1992)
INSERT INTO @table VALUES(1183071, 3, 1991)
INSERT INTO @table VALUES(1183071, 5, 1991)
INSERT INTO @table VALUES(1183071, 9, 1991)
INSERT INTO @table VALUES(1183071, 3, 1992)
INSERT INTO @table VALUES(1097890, 9, 1995)
INSERT INTO @table VALUES(1097890, 4, 1995)

Select *, DateAdd(Year, [year] - 1900, DateAdd(Month, [Month]- 1, 0))
From @Table

If you 'create' an actual date before you do the min, the query becomes a lot easier.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Thiesen]

Thank's Paul, but i'm forget to say: The table have near 12000 lines (1156 ID x 12 month x 12 year), and i dont know when the person was included in the table.

 

[ptheriault]

That's awesome George, Now it becomes much easier to select the min year. like this?

Select ID
, MIN(DateAdd(Year, [year] - 1900, DateAdd(Month, [Month]- 1, 0)) )
From @Table
GROUP BY ID

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[gmmastros]

Yup. My query ended up looking like this...

Select Id, 
       Month(Min(DateAdd(Year, [year] - 1900, DateAdd(Month, [Month]- 1, 0)))) As Month,
       Year(Min(DateAdd(Year, [year] - 1900, DateAdd(Month, [Month]- 1, 0)))) As Year
From   [!]@Table[/!]
Group By Id

Thiesen, Replace @Table in the query above with your actual table name.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ptheriault]

Thanks for the Lesson George. You Da Man!


- Paul
- If at first you don't succeed, find out if the loser gets anything.