help with SQL script - npc item duplications

[heyitsmexd]

hi.
i am currently struggling with removing items from vendors that have the same display id. i could obviously do it manually but since ive made plenty of them it would take hours at this point.
here's an example: Imgur: The magic of the Internet - first 8 items on the list have exactly the same displayID but i only want to keep/insert one of them and delete the other 7
how could i filter that with an sql script?

 

[ExO]

Hmm. I see what you mean.

I mean one what to do it, is to set a limit in the end, like limit x. But it will still result in some manual work if the amount of items aren't always equal(?).

Maybe this will help:
mysql - SQL query: Delete all records from the table except latest N? - Stack Overflow

 

[Damieninabox]

I wonder what's causing that issue, that's not a regular V13 issue right?

 

[heyitsmexd]

so i wrote this, but it doesn't work. not really sure how to make it skip entries with same displayid

SET
@npcid = 500110;
DELETE FROM npc_vendor WHERE entry = @npcid;
INSERT INTO npc_vendor (entry, slot, item, maxcount, incrtime, extendedcost, type)
SELECT @npcid, 0, entry, 0, 0, 3872, 1 FROM item_template WHERE class = 4 AND inventorytype = 1 AND itemlevel > 20 AND itemlevel < 402 AND allowableclass != -1 AND itemset != 0 ORDER BY displayid DESC LIMIT 1;

also [MENTION=27778]Damieninabox[/MENTION] this is no bug or anything im just creating custom vendors and just trying to filter certain items out

 

[heyitsmexd]

i found a method that was quite simple and works just as i wanted

SET
@npcid = 500110;
DELETE FROM npc_vendor WHERE entry = @npcid;
INSERT INTO npc_vendor (entry, slot, item, maxcount, incrtime, extendedcost, type)
SELECT @npcid, 0, entry, 0, 0, 3872, 1 FROM item_template WHERE class = 4 AND inventorytype = 1 AND itemlevel > 20 AND itemlevel < 402 AND allowableclass != -1 AND itemset != 0 GROUP BY displayid;

the magic words "group by" will do the trick