1

I'm working on mysql ,and I got some duplicate rows in a view. The view has simply image table data, and product data. Now What I want is to get the only one column in the same id. (I tried to make unique key but it's view so it couldn't work.)

Here is my query.

SELECT id, status_pub, name, code, 
       catch_copy, icon_new, icon_recommended, 
       u_date, order_num, st_time, ed_time, 
       isNULL(order_num) AS order_num_null, filename 
FROM TEST 
WHERE id IN (SELECT DISTINCT data_id 
             FROM R_CATEGORY 
             WHERE category_id REGEXP '^001') 
AND ((st_time IS NULL AND ed_time IS NULL) 
 OR (NOW() BETWEEN st_time AND ed_time) 
 OR (st_time IS NULL AND ed_time > NOW()) 
 OR (ed_time IS NULL AND st_time < NOW()) ) 
AND status_pub = 1     
ORDER BY order_num_null ASC, order_num ASC, u_date

Here you can see duplicate 3 duplicate 'id 48'. I want to retrieve the first one. Such as . . 46, 47, 48, 49, . .

NOT . . 46, 47, 48, 48, 48, 49,

enter image description here

NOW I EDIT. I added the DISTINCT STATEMENT, But the result is After adding distinct statement

Toshi
  • 6,012
  • 8
  • 35
  • 58

1 Answers1

0

You can remove filename column which has different values for same id value and store the output in a temp table with filename column having null

Update this column in the temp table later with top value from the view joining with id

radar
  • 13,270
  • 2
  • 25
  • 33