Order SQL Table Data Help

[whitebark]

I need to order some sql data in a table to weed out any duplicate ip addresses. The table looks like the one below. Can it be done with the other data in the table as is?

What would the sql command be?

CREATE TABLE `PLD_BANLIST` (
`ID` int(11) NOT NULL auto_increment,
`BAN_IP` varchar(15) default NULL,
`BAN_DOMAIN` varchar(64) default NULL,
`BAN_EMAIL` varchar(255) default NULL,
`BAN_WORD` varchar(255) default NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=868 DEFAULT CHARSET=latin1 COMMENT='Stores banning informations' AUTO_INCREMENT=868 ;

--
-- Dumping data for table `PLD_BANLIST`
--

INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (1, NULL, NULL, NULL, 'celexa');
INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (2, NULL, NULL, NULL, 'lexapro');
INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (3, NULL, NULL, NULL, 'zoloft');
INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (4, NULL, NULL, NULL, 'prozac');
INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (5, NULL, NULL, NULL, 'cialis');
INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (6, NULL, NULL, NULL, 'conan');
INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (7, NULL, NULL, NULL, 'levaquin');
INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (8, NULL, NULL, NULL, 'levitra');
INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (9, NULL, NULL, NULL, 'rolex');
INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (10, NULL, NULL, NULL, 'pharmacy');
INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (11, NULL, NULL, NULL, 'sexcam');
INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (12, NULL, NULL, NULL, 'warcraft');
INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (13, NULL, NULL, NULL, 'warhammer');
INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (14, NULL, NULL, NULL, 'wow');
INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (15, NULL, NULL, NULL, 'semenax');
INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (16, NULL, NULL, NULL, 'sexuale');
INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (17, NULL, NULL, NULL, 'spermei');
INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (18, NULL, NULL, NULL, 'orgasme');
INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (19, NULL, NULL, '@126.com', NULL);
INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (20, NULL, NULL, '@163.com', NULL);
INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (21, NULL, NULL, '@21cn.com', NULL);
INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (22, NULL, NULL, '@aol.in', NULL);
INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (23, NULL, NULL, '@inbox.ru', NULL);
INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (24, NULL, NULL, '@gawab.com', NULL);
INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (25, NULL, NULL, '@narod.ru', NULL);
INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (26, NULL, NULL, '@i.ua', NULL);
INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (27, NULL, NULL, '@mail.ru', NULL);
INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (28, NULL, NULL, '@yandix.ru', NULL);
INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (29, NULL, NULL, '@yandex.ru', NULL);
INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (30, NULL, NULL, '@yandex.com', NULL);
INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (31, NULL, NULL, '@yahoo.in', NULL);
INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (32, NULL, NULL, '@drinkrelated.com', NULL);
INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (33, NULL, NULL, '@mail.bg', NULL);
INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (34, NULL, NULL, '@tut.by', NULL);
INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (62, NULL, NULL, '@qq.com', NULL);
INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (703, '87.212.124.73', NULL, NULL, NULL);
INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (301, '115.184.98.42', NULL, NULL, NULL);
INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (39, '213.217.113.3', NULL, NULL, NULL);
INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (162, '219.64.85.237', NULL, NULL, NULL);
INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (41, '222.95.8.47', NULL, NULL, NULL);
INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (245, NULL, NULL, '@digg.com', NULL);
INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (163, '125.63.90.34', NULL, NULL, NULL);
INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (44, '92.41.207.13', NULL, NULL, NULL);
INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (45, '88.246.215.157', NULL, NULL, NULL);
INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (731, '180.151.74.2', NULL, NULL, NULL);
INSERT INTO `PLD_BANLIST` (`ID`, `BAN_IP`, `BAN_DOMAIN`, `BAN_EMAIL`, `BAN_WORD`) VALUES (47, '61.19.67.92', NULL, NULL, NULL);

Thanks!

 

[RLW]

I'm not exactly sure what you mean. If you don't want any duplicate results, you could always do:

SELECT DISTINCT BAN_IP FROM PLD_BANLIST

and if you want that ordered by IP address:

SELECT DISTINCT BAN_IP FROM PLD_BANLIST ORDER BY BAN_IP

Now, if you want other fields in the results, you could try something like:

SELECT * FROM (SELECT DISTINCT BAN_IP FROM `PLD_BANLIST`) AS t1 LEFT JOIN `PLD_BANLIST` USING (BAN_IP)

I'm not sure if that's the most efficient way to do it - I'm not a professional - but it does work!

 

[nasaboy007]

If you want to save processes and never want to have duplicate IPs (so only the first entry with that IP is saved) is to just set the IP column as UNIQUE.

 

[whitebark]

Well let me try to explain this in some more detail - I want to order the existing data so that I can filter out near duplicates in the table.

i.e. - 99.99.99.100
99.99.99.101
99.99.99.102
99.99.99.106

If they are ordered in a sequential manner I can use the online admin interface to weed those down to 99.99.99.*

Hope that makes more sense.

Thanks!