How to get same data together from table in ASP.NET

Question

0.00/5 (No votes)

See more:

Hello, my title may be not clear but my problem is:
i have some data in my sqltable
eg

index	timedata	field1	field2
1	31/10/2017 00:00	a	x
2	01/11/2017 00:00	a	x
3	02/11/2017 00:00	a	x
4	03/11/2017 00:00	b	y
5	04/11/2017 00:00	b	y
6	05/11/2017 00:00	b	y
7	06/11/2017 00:00	a	x
8	07/11/2017 00:00	a	x

in this data i want to index 1,2,3 together 4,5,6 together and 7,8 together
field1 and field2 are used for where claues
my final result should be

index	from date	          todate	field1	field2
1	31/10/2017 00:00	02/11/2017 00:00	a	x
2	03/11/2017 00:00	05/11/2017 00:00	b	y
3	06/11/2017 00:00	07/11/2017 00:00	a	x

here result of 1,2,3 and 7,8 should not mix

What I have tried:

i am fetching data in my sqldataadapter

Posted 31-Oct-17 0:23am

Nitin Surya

Updated 31-Oct-17 3:29am

Add a Solution

Comments

Atlapure Ambrish 31-Oct-17 6:54am

Not clear, maybe you want to use 'Improve question link to add more details on what you are looking for. With your explanation above, can't you just use where clause with IN and mention the index values you want to select data for?

Nitin Surya 31-Oct-17 7:08am

see, i need to get first and last entry for same field1 and field2. as in above eg. index 1,2,3 have same field1, field2 entry, index 4,5 have same entry see my expected result table, from date is first entry and todate is last entry.
that way i need to make it all same consecutive entries.
In my eg. index 1,2,3 and 7,8 have same field1 and field2 since they are not together so they should be treated as separate result...
if you see my expected result you will understand the scenario..

Karthik_Mahalingam 31-Oct-17 23:11pm

use Reply button to post comments/query to the concerned user, so that the user gets notified and respond to your text.

2 solutions

Solution 2

you can try following query,

select rownum "index",time_stamp,field1,field2 from
(
select min(indexnum) indexnum,min(time_stamp) time_stamp,field1,field2
from table_name
group by field1,field2
) a;

Posted 31-Oct-17 3:29am

Member 12088223

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)

Sibasisjena · Accepted Answer · 2017-10-31T01:36:00

Below links are worth looking into. Please try below tricks.
sql - How can I find duplicate consecutive values in this table? - Stack Overflow[^]
sql - How to find consecutive rows based on the value of a column? - Stack Overflow[^]
sql server - Count consecutive duplicate values in SQL - Stack Overflow[^]