I was wondering, I have a similar posting on a SQL forum site, it has been there 2 days and it must not get the kind of traffic and attention this site gets. SO I am hoping someone on here would be willing to guide me in the right direction.
I am trying to create a Microsoft SQL query that will go thru a table and it will check the the status of the MECHPG column. Everywhere where the MECHPG column changes state the query will pull that row of data and only where it changes state.
The value of the MECHPG column is either a 1 (one) or a 0 (zero) Nothing else. The MECHPG column can have data as follows:
0
0
0
1 <—
1
1
1
1
0 <----
0
0
0
1 <----
1
0 <----
etc…
If the above data was in a table and the query was ran the query would pull in the row of information at every place where you see an arrow. All other data would be ignored. Just the data where a change has occured.
So what is the easiest way to accomplish this using standard SQL transact statements?
Thanks to all.
Hi mrtweaver,
Interesting question. I achieved this by combining 2 copies of your table in a JOIN, offsetting the second copy by one:
SELECT t1.MyIndex, t1.MECHPG
FROM MyTable t1 LEFT JOIN MyTable t2
ON t1.MyIndex=t2.MyIndex+1
WHERE t1.MECHPG<>t2.MECHPG
I have assumed an index field MyIndex which increments by 1 for each record. This was done using MySQL - there may be some slight changes to use it with MSSQL.
Let me know if this helps.
Al
Yep, the only way I know to do this is to join a table with itself just like Al suggested. Only works if your key values don’t skip any numbers, but that is usually the case.
Al, thank you. Your idea works well in MySQL. Still trying to work out the differences between MySQL and MSSQL. For some reason MSSQL does not like to easily display the Index numbers. I have asked at several MSSQL forum sites and this seems to be concensus. SO How can I adapt this code to work with MSSQL, dont know yet but hope to find out soon. Been looking at all these notes and everything that I done over the weekend at home. Any ideas will be entertained.
So how about it Carl, care to take a stab at the conversion between MySQL and MSSQL?
[quote=“AlThePal”]Hi mrtweaver,
Interesting question. I achieved this by combining 2 copies of your table in a JOIN, offsetting the second copy by one:
SELECT t1.MyIndex, t1.MECHPG
FROM MyTable t1 LEFT JOIN MyTable t2
ON t1.MyIndex=t2.MyIndex+1
WHERE t1.MECHPG<>t2.MECHPG
I have assumed an index field MyIndex which increments by 1 for each record. This was done using MySQL - there may be some slight changes to use it with MSSQL.
Let me know if this helps.
Al[/quote]
This idea has nothing to do with MySQL displaying indexes - “MyIndex” in the example is simply an auto-incrementing primary key of the table.
Dang, why didnt I catch that. Man is my brain fried, i keep putting to much thought into things instead of reading closer. Thanks Carl. It really helped and I got it to the point where it appears to be working. And a big thanks to Al as well.