15,029,433 members
See more:
I have a table with the following structure
```   id   portid     Daynumber
1   334     1
1   335     2
1   337     3
1   334     4
2   333     1
2   338     2
2   333     3
3   333     1
3   338     2
3   333     3
4   456     1
4   789     2
4   700     3
5   456     1
5   789     2
```

I need to get the result as where first day of the planid and the last day of the plan ids port is same
```id      portid          Daynumber
1   334     1
2   333     1
3   333     1
```

Help me through SQl Query
Posted
Updated 3-Jan-14 23:42pm
v2

## Solution 2

```with minValue as
(
select id, portid from tbl1 where daynumber = 1
),
maxValue as
(
select id, portid, daynumber from tbl1 where daynumber = (select max(daynumber) from tbl1 t where tbl1.id = t.id)
)

select t1.id, t1.portid, t2.daynumber from minValue t1 inner join maxvalue t2 on t1.id = t2.id and t1.portid = t2.portid```

That should work. It gets the minimum and maximum values and only returns situations where the port id is the same on the first and last day.

## Solution 3

SQL
```SELECT * FROM dbo.tabPlan a WITH (NOLOCK)
INNER JOIN (SELECT MIN(Daynumber) as Daynumber ,id FROM dbo.tabPlan WITH (NOLOCK)
GROUP BY ID ) AS b
ON a.id = b.id AND a.Daynumber = b.Daynumber```

## Solution 1

I can't make sense of this question. I can't see any logic by which those three values are all you want, nor does this seem to be the same as what you're saying. right(cast (portid as varchar(5)), 1) will give you the last digit, but I don't see how the day number is matching that, in your sample data.
sacraj 5-Jan-14 22:44pm

I would like to get the portid's which are same. for example
id portid Daynumber
1 334 1
1 335 2
1 337 3
1 334 4

in this above sample the portid is same for the day number 1 and 4 so I need to get the port ids which has same port ids for the first and last day.
Christian Graus 5-Jan-14 22:47pm

So, to be clear, if row 3 was 334 and row 4 was 337, you would not want this id returned ? Do you need id, portid and max daynumber, is that it ? What version of SQL Server do you have ? is it really 2005 ? That really sucks.