Click here to Skip to main content
13,795,745 members
Rate this:
 
Please Sign up or sign in to vote.
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 21:45pm
Updated 16-Mar-13 3:39am
v5
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 :)
   
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
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

  Permalink  
Comments
nika2008 30-Aug-12 5:43am
   
dude thnx alot this is what i need
Arunprasath Natarajan 30-Aug-12 12:55pm
   
:)
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

first make a string of Nums u want to fetch from sql.
eg.
string Nums = "5,8";


sql query is like below
select * from tbl where Num in (5,8)


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

Happy Coding!
:)
  Permalink  
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
:)

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web02 | 2.8.181207.3 | Last Updated 16 Mar 2013
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100