Sql Query Help to get the same port details

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

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 3-Jan-14 23:40pm

sacraj

Updated 3-Jan-14 23:42pm

Aarti Meswania

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Christian Graus · Answer 1 · 2014-01-05T17:03:00

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.

P.S Vijay · Answer 2 · 2014-01-05T18:25:00

Solution 3

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

Posted 5-Jan-14 18:25pm

P.S Vijay

Christian Graus · Answer 3 · 2014-01-04T00:01:00

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.

Posted 4-Jan-14 0:01am

Christian Graus

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.