i need SQL HELP>!< sql query

Question

2.60/5 (5 votes)

See more:

i have tabel with this data
ID NUM
1 1
2 4
3 5
4 6
5 7
6 10
7 3
8 5
9 9
10 11
12 13

now i wanna query how will return this colons
ID Num
2 4
5 7
10 11
11 12
12 13

that what i need to return is the first and last number of numbers how are in array
exp. if we have 3 5 6 7 8 10 i need the return the 5 and 8
sorry for my bad english
thnx alot for help

Posted 29-Aug-12 20:45pm

nika2008

Updated 16-Mar-13 2:39am

v5

Add a Solution

Comments

Santhosh Kumar Jayaraman 30-Aug-12 2:52am

Your question is not clear ?How can you expect 5,7 to be printed when its in sequence

nika2008 30-Aug-12 3:00am

what information u need for more clear to be?

Santhosh Kumar Jayaraman 30-Aug-12 3:07am

check your input and output and the scenario you said..Its not at all matching

Zoltán Zörgő 30-Aug-12 2:56am

Ok, but you need to do this in SQL, or in C#? Actually I can not imagine a standard query statement, that can do this for you. A logic is needed, that can be made in C#, and with hard work also in an SQL server stored procedure also.

nika2008 30-Aug-12 2:59am

i need it on sql i done query who return the array of numbers that will return me the 5 6 7 8 but i cant modifat it to give me the first and last??
any suggestrion

CodeHawkz 30-Aug-12 3:01am

We have your data; it's clear. Give us 3 examples of sample input and expected output, so we can work out the logic that we can't get by reading your question :)

D-Kishore 30-Aug-12 3:05am

Give us clear example of input and output of data

Aarti Meswania 30-Aug-12 3:17am

not a clear question
do you want to get second value from both end of array? 3 5 6 7 8 10 -> 5,8
and then
do you want to filter sql data on condition for that filtered values (eg. here 5,8) ?

nika2008 30-Aug-12 3:21am

no dude i need the first and last number who are in arrays for exp

1 5 8 9 10 15 17 18 19 21 25 in this datas i need to select the 8 ,10 , 17 , 21

did you understned it if we fine the number who are 1 2 3 then return frist and last 1 , 3 or if we fine 5 6 7 8 then return 5 and 8

Aarti Meswania 30-Aug-12 3:35am

ok then
as per above e.g.
if we fine 5 6 7 8 then return 5 and 8
means you have already retrieve 5 & 8 and now you want to fetch data from sql which have id = 5 & 8 right?

nika2008 30-Aug-12 3:47am

not ID but from the NUM colon

Legor 30-Aug-12 4:51am

But the first and last numbers of your example 1 5 8 9 10 15 17 18 19 21 25
are 1 and 25 ?

nika2008 30-Aug-12 4:57am

the first and last numb of tabel i say of array the number who are 1 2 3 4 and return 1 4

Zoltán Zörgő 30-Aug-12 5:05am

Than again: you have a table that contains some values and an ID. There is no duplication, but there are gaps. You want the beginning and the end of continuous intervals. Right?

nika2008 30-Aug-12 5:12am

yes but no on ID colon but on Num colon and no gaps and null values

Zoltán Zörgő 30-Aug-12 5:25am

I understood. You need a single query statement or T-SQL? Anyo other SQL Server object can be used? Is this a homework?

nika2008 30-Aug-12 5:27am

i need single query but and T-sql in not bad is like homework

Zoltán Zörgő 30-Aug-12 5:35am

Well, we don't do homework, bit this is an interesting one. One more question: If you have 1 2 3 5 7 8 9, than you need 1 3 and 7 9, but what is with 5? It is a one element long sequence. You want that also, or only sequences with at least two elements?

nika2008 30-Aug-12 5:37am

the 5 dont need to return

Zoltán Zörgő 30-Aug-12 5:39am

Check first the link I have posted. Don't be confused, the ID fild there is good for your Num field.

nika2008 30-Aug-12 5:40am

i done query to fine only continuous intervals number but i cant do it begin and end

2 solutions

Solution 1

first make a string of Nums u want to fetch from sql.
eg.

C#

string Nums = "5,8";

sql query is like below

SQL

select * from tbl where Num in (5,8)

equivalent C# code

C#

cmd.commandtext = "select * from tbl where Num in (" + Nums + ")";
// where 'Nums' is variable having list of selected numbers separated by comma

Happy Coding!
:)

Posted 29-Aug-12 22:06pm

Aarti Meswania

v2

Comments

Legor 30-Aug-12 4:51am

As far as it's understandable from OPs explanation this should be right.

Aarti Meswania 30-Aug-12 4:53am

Thank you! :)

nika2008 30-Aug-12 4:55am

i dont think so. i dont need filter 5 and 8 i need first and last elemnt who are in array for exp 6 2 7 8 9 10 6 9 and as return is 7 and 10

need the firs and last of numbers who are 7 8 9 10 and return 7 and 10

Aarti Meswania 30-Aug-12 5:01am

I have tried my best to help you,
that isn't my fault if you can not explain your requirements clearly.
just see comments below question we all want to help you but still your question is not clear as per our last three comments below your Question I can only understand which i have explained in my answer.
this way how could anyone help you?

Legor 30-Aug-12 5:02am

Im sorry i realy try to understand what you mean but i just dont get it.
And the 5,8 of this solution was just an example... the author was making the assumption you allready got those values beforehand.

Aarti Meswania 30-Aug-12 5:07am

no dear Legor, msg wasn't for you it was for nika2008. I have reply him not you, I m really thankful you for upvoting. :)
you are also in same situation, we can't get what is actual problem nika2008 have, he can't explain it properly.

Legor 30-Aug-12 5:12am

Yes i know i answered to nika also. But in the time i was writing you also answered him.

nika2008 30-Aug-12 5:10am

thnx for try to help me i will try to make it solo

Legor 30-Aug-12 5:12am

You can also try to make it more clear.

nika2008 30-Aug-12 5:18am

i want the beginning and the end of continuous intervals numbers

Aarti Meswania 30-Aug-12 5:19am

we all are trying to help you by our best effort
never down-vote answers in case you are not able to explain it
code project team always help each other
it is also good to solve problem your self but if you will make question more easier to understand then problem will be solved in less time
:)

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)

**Zoltán Zörgő** · Accepted Answer · 2012-08-29T23:37:00

Solution 2

This looks alike: http://www.xaprb.com/blog/2006/03/22/find-contiguous-ranges-with-sql/[^]

Posted 29-Aug-12 23:37pm

Zoltán Zörgő

Comments

nika2008 30-Aug-12 5:43am

dude thnx alot this is what i need

Arunprasath Natarajan 30-Aug-12 12:55pm

:)