hello,

can't figure out this query, any help appreciated

i need to order all duplicates by their unique id (`nid`)......then
take those duplicates and reduce them to only one record per `nid`
value. the remaining values are then checked to see if a particular
column (`bv`) is less than a value. these remaining values are then
used to mark a column in a separate table (`dltme`) to 1

example:

table1 (has duplicates):
id nid value1 value2 value3 dltme
1 01-1 0.32 0.45 0.11 0
2 01-3 0.12 0.75 0.31 0
3 01-4 0.37 0.25 0.17 0
4 01-1 0.32 0.45 0.11 0
5 01-1 0.32 0.45 0.11 0
(duplicate id's : 1,4)

step #1
=> (duplicates)
1 01-1 0.32 0.45 0.11 0
4 01-1 0.32 0.45 0.11 0
5 01-1 0.32 0.45 0.11 0

step #2
=> (reduce)
1 01-1 0.32 0.45 0.11 0

step #3
=> (check, value3 < 1.0)
1 01-1 0.32 0.45 0.11 0

step #4
=> (mark dltme on whichever id# has nid=01-1 in TABLE2 to 1)


table2 (has no duplicates):
id nid value1 value2 value3 dltme
1 01-1 0.32 0.45 0.11 1
2 01-3 0.12 0.75 0.31 0
3 01-4 0.37 0.25 0.17 0


if you need more explanation ill try - i just can't get this to
work.....thanks!!

You may try something in this direction :

INSERT INTO table2
SELECT * FROM table1
WHERE value3 < 1.0
GROUP BY nid
HAVING count(*) > 1
UNION
SELECT * FROM table1
GROUP BY nid
HAVING count(*) = 1

jero


On 29 juil, 04:05, inexion <abslo...@gmail.com> wrote:
> hello,
>
> can't figure out this query, any help appreciated
>
> i need to order all duplicates by their unique id (`nid`)......then
> take those duplicates and reduce them to only one record per `nid`
> value. the remaining values are then checked to see if a particular
> column (`bv`) is less than a value. these remaining values are then
> used to mark a column in a separate table (`dltme`) to 1
>
> example:
>
> table1 (has duplicates):
> id nid value1 value2 value3 dltme
> 1 01-1 0.32 0.45 0.11 0
> 2 01-3 0.12 0.75 0.31 0
> 3 01-4 0.37 0.25 0.17 0
> 4 01-1 0.32 0.45 0.11 0
> 5 01-1 0.32 0.45 0.11 0
> (duplicate id's : 1,4)
>
> step #1
> => (duplicates)
> 1 01-1 0.32 0.45 0.11 0
> 4 01-1 0.32 0.45 0.11 0
> 5 01-1 0.32 0.45 0.11 0
>
> step #2
> => (reduce)
> 1 01-1 0.32 0.45 0.11 0
>
> step #3
> => (check, value3 < 1.0)
> 1 01-1 0.32 0.45 0.11 0
>
> step #4
> => (mark dltme on whichever id# has nid=01-1 in TABLE2 to 1)
>
> table2 (has no duplicates):
> id nid value1 value2 value3 dltme
> 1 01-1 0.32 0.45 0.11 1
> 2 01-3 0.12 0.75 0.31 0
> 3 01-4 0.37 0.25 0.17 0
>
> if you need more explanation ill try - i just can't get this to
> work.....thanks!!