Click here to Skip to main content
15,029,433 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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

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.
   
Please take a look.
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
   
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.
   
Comments
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.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900