Matching Records in a record set?

[Hondy]

Hi

I have some code that pulls records back from a database and I want to highlight a row if the records matches another record from the same data set.

Doing a simple if statement works if I want to pick out a particular record, but I haven't a clue how to match another record, do I need to make an array or something?

an example:

red, 123, aaa, bbb ...
blue, 321, bbb, ccc ...
green, 098, ddd, eee ...
blue, 543, fff, ggg...

in column 1 the word blue is found in row 2 and 3 so I want to make an IF statement

IF column1 (recordany) = column1 (recordany) then ...

thanks

 

[Hondy]

correctionm rows 2 and 4

 

[BigRed1212]

Is column 1 the only matching column you are concerned about?

What happens if 2 records in column 1 are blue and 2 are red? Will they all 4 get the same highlight?

If you simply sorted your SQL output (order by 1) would that provide the visual matching you need?

 

[Hondy]

If 2 are red and 2 are blue then I will have 4 records highlighted. I used colours as an example, I just want to highlight pairs basically.

Does this make it clearer?

Thanks

 

[ggriffit]

Sounds like you need to do a nested loop here is some pidgeon JS as Pseudo Code :

for (i=0;i<rows.length;i++)
{
for (j=0;j<rows.length;j++)
{
// if the row value matches
// and we are not looking at the same row
if ((rows==rows[j]) && (i<>j)
{
alert("Row " + i + " matches Row " + j);
}
}
}

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/

 

[tsuji]

If you want to taking profit of the efficiency of the functionality built into the sql, you can execute this sql. I suppose the table name as [tbl], the color column is named [color] and no other filtering conditions - you can insert them if any, accordingly. The alias [highlight] column has a boolean value which would determine whether it should be "hightlight" by the application.
[tt]
ssql="select *, (select cbool(count(*)>1) from [tbl] as t2 where t2.color=t1.color group by t2.color) as highlight from [tbl] as t1"
[/tt]

 

[Hondy]

thanks all, doen't make a whole lot of sense to this techy!

Greg, can you explain the code a bit further? Thanks tsuji, I want to avoid dabbling in sql if I can.

 

[tsuji]

>I want to avoid dabbling in sql...
You're sure? I can leave you in peace.

 

[Hondy]

well, actually, would that work on a sql view called from ASP?

 

[Hondy]

or maybe i can use the ado.recordset moveprevious? can anyone tell me how I can use it?

Thanks

 

[ggriffit]

the Pseudo Code for my function was as follows :

For each record (i) in the resultset
For each record (j) in the resultset
// if the values match
// and the records are not the same record
if (record==record[j]) AND (i<>j) then
then we have two records with matching data
end
Loop
Loop

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/

 

[ggriffit]

the Pseudo Code for my function was as follows :

For each record (i) in the resultset
For each record (j) in the resultset
// if the values match
// and the records are not the same record
if (record==record[j]) AND (i<>j) then
then we have two records with matching data
end
Loop
Loop

Thinking about it if you have a large dataset I would do it in the database as suggested by others.

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/

 

[PaulBricker]

Why not do the matching in the database first (using a totals query) and then pull the info from that result set into your asp page?

Paul

 

[Hondy]

Greg - should that code be within the repeated region or afterwards?

Paul - the data is matched up by the SQL code already, items appear in pairs as I need but the trick is making them highlight using html.

The problem is because the there is only one row in the ASP and this is repeated for each record, so one record is not aware of the previous record.

In lay, what I could do with happening is:
"if my cell value is the same as the previous cell value then make the current row colour red"

Does that make sense?

Thanks

 

[Hondy]

hi, I figured out how to do what I need, using the moveprevious command.

Thanks for your input

 

[DreXor]

Hondy, sorry for the delayed response, but using move previous is rather taxing work for a web page...

secondly (i'll cover in depth later) using the same color to highlight everything is self defeating.

you can do something like this :

prev = ""

do while not rs.eof
if rs(field) <> prev then
switch color function method, whatever, usually applied to the TR or TD
else
reprint previuous color scheme
end if

Extra data output here, blah blah,

prev = rs(field)
rs.movenext


note i denoted the use of a color switcher, like ledger paper or greenbar, reason being is alternating records will flip colors, no biggie, repeats will land in the same color block, this i believe is easier on the eyes and to interpret .. give you an example

you have 100 records on screen of which there are at least 2 of each type, meaning ALL records shown will be highlighted... with your initial method everything is highlighted the same and basically negates all the work you did. vs with alternating you can have 4 sets of 25 and they are easily and visually identified in those blocks with alternating colors

color switcher is easy enough too

public color
function colorswitch()
color1 = blue
color2 = green
if color = color1 then
color = color2
else
color = color1
end if
colorswitch = color
end function


then all you do is <tr bgcolor="<%=colorswitch()%>">
and in the case of repeat the color it's just <tr bgcolor="<%=color%>">


DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!
" I always think outside the 'box', because I'm never inside the 'loop' " - DreX 2005

 

[DreXor]

note dont forget to put the public variable way up top before executing any script

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!
" I always think outside the 'box', because I'm never in the 'loop' " - DreX 2005