MYSQL :: Selecting Non Duplicate Records Without Using DISTINCT

DISTINCT Still Showing Duplicate Records
Why won't distinct work? The sql IS selecting correctly but its showling records with the same f.preplyid

CODE

SELECT DISTINCT (f.preplyid), f.pid, f.ptid, f.ptid2, f.puid, f.psubject, f.ptype, f.ptime, t1.tid, t1.tname tname1, t2.tid, t2.tname tname2 FROM forumpost as f, thread as t1, thread as t2 WHERE t1.tid = f.ptid AND t2.tid = f.ptid2 AND f.puid =2 AND f.ptype=1 AND f.puid NOT IN (SELECT fp.puid FROM forumpost as fp WHERE fp.ptype=0 AND fp.pid=f.preplyid) ORDER by f.ptime desc LIMIT 7

How Can I Use The DISTINCT (or Another Argument) To Choose Duplicate Rows In My DB?
I have a form where users submit their information. Unfortunately, some people tend to submit the form twice, sometimes 3 times. Is there a way I can select duplicate rows in my table?

Selecting DISTINCT Except For One
There's this membership script, I am adding usergroups to it and so I made a new field called groups and everything. Now I want to add it to the admin profile update page. I'd like it to be in a dropdown menu and I already know how to do that, using the distinct function and everything. But I'd like the user's current usergroup to be the default on the dropdown menu, so I know which one they are now.

I was going to retrieve the user's current group but if I get the groups from the whole list as well, it would come out twice wouldn't it?

Selecting Distinct
Can someone please tell me how I can do this:

I have an appointment table which has appointment_id, client_id,
appoinment_time (as unix timestamp). Clients can have multiple
appointments.

I want to select all appointments from the appointment table but only
one appointment (the latest one) for clients that have multiple
appointments.

Selecting Distinct Rows
I have a mysql query which I am trying to select distinct information with.

Here is the query:

Selecting The Inverse Of Distinct
I would like to select the fields that exists more than one time.

Description of my table is:

Create table MYTABLE (CLASS_ID char(2),STUDENT_ID char(2));

I need to select the students that are in more than one class!

Selecting A Distinct Query
I want to: select name, address, city, state, zip from client;

However I want to query a distinct address(some of the people work at the same address, and I only want one letter to go to each address.).

Is it something like:
select name, distinct[address], city, state, zip from client;

I know this is not right, but does anyone know this? Thx.

Selecting Distinct Names
I am attempting to create a list of servers that have distinct usernames, and if there are multiple servers with the same name, show the one with the lowest ID.

Here is my existing SQL but the DISTINCT does not work:

SELECT DISTINCT(name),date_generated,location,pass_validation,ID,time_saved FROM SQL_SVA.servers

Selecting Distinct Results In A Join
SELECT p.post_title
, p.post_name
, c.cat_name
, DATE_FORMAT(p.post_date, '%M %D, %Y') AS dateadded
FROM wp_posts AS p
LEFT JOIN wp_post2cat AS pc ON p.ID = pc.post_id
LEFT JOIN wp_categories AS c ON pc.category_id = c.cat_ID
WHERE MATCH(p.post_content, p.post_title)
AGAINST(TRIM('merchant') IN BOOLEAN MODE)
ORDER BY p.post_date DESC
I am doing a fulltext search on a Wordpress database. Some posts are in multiple categories. When using the above query I will get a result for every category a post is in. I don't want that. I have tried using distinct with no effect. The wp_categories has a row for each category a post is in and how I am joining it seems to be the culprit.

SELECTing Distinct Values Over Multiple Columns
I have a table setup for populating a drop down menu.

CREATE TABLE catagory
( ID char(10) PRIMARY KEY NOT NULL,
cat1 varchar(50),
cat2 varchar(50),
cat3 varchar(50) );

Now each row is for one client to populate so that he can be listed under multple catagorys.

The problem comes in that I need DISTINCT catagories but say the client with ID 00000-0001 has a cat1 of greek, and a cat2 of pizza, where on the next, a client with a different ID has a cat1 of italian, but a cat2 of pizza. How would I word the query so that I can return distinct results from both rows AND columns or is this a problem on the PHP side of things (how i'm coding the page)?

as it stands right now, my query of:
SELECT DISTINCT cat1, cat2, cat3 FROM catagory;
isn't working very well (I get all the results, as if the DISTINCT wasn't there)

Selecting Unique Values From A Table (NOT DISTINCT!)
Lets say we have a table that has the field photo_id and has values of it many times. For example 245 can exist any number of times (1, 5, 8, e.t.c.).

I want to select the values that exist in the table ONLY ONCE. Please note that i dont want to select each value once (using DISTINCT), that would return all the values that exist in the table.

For example lets say the table has the following values:

245
563
776
224
563
563
776
776
776
776

The query i want to make should return only the values 245 and 224 as 563 and 776 exist many times in that table.

Selecting Amount Of Rows With Distinct Values In Fields
how do i select the amount of rows in table that have a distinct field value.
example: in my table i have a column (column a) that sometimes has duplicate values (sometimes 2 duplicates, sometimes more, sometimes no duplicates).

how do i get the amount rows in the table that have a unique/distinct value in their column a (without the duplicates rows)?

Distinct Records
Distinct records not working, I still get duplicate's

$query = "SELECT DISTINCT(ITEM), `NAME` , `PRICE` , `IMG`, `id` FROM `iflorist` WHERE `PRICE` >= 0 and `PRICE` <= 29.99 ORDER BY `NAME` DESC LIMIT 0 , 999";

If I take out the "id" field no duplicates will output, But I need "id" included.
"id" is setup currently with : INT, INDEX, and auto_increment ??

Selecting Multiple Columnn Values Into 1 Distinct/unique List
I have a table with several fields for email (primary email, alternative email, contact email)

Some times, some of these fields will be blank and sometimes the same address will be enter for both primary and contact.

I use this table and these fields to do a mailout but I don't want to mail people twice because there address occurs in more than 1 column.

I need to end up with a list containing all email values from those 3 columns that is unique (no duplicates).

I can do this by checking each one and putting it into a php array but thought there may be a nicer way to do it in the SQL?

Duplicate Records
I've noticed what might be a bug. I'm using InnoDB tables to store some payment information. Included in the table is a DATETIME field that logs the exact time a payment was made to a client. The problem is that in one instance there are clearly duplicates of a payment record in the table. i.e. 3 records were inserted that have the exact same information including the same timestamp. I have checked the code making the INSERTs and it can't be that. It's just a simple INSERT query. Has anyone else noticed things like this and is there a solution?

Duplicate Records
mysql> select id, student_first_name, student_last_name, email,
application_date, modification_date, unique_key from student where id
in (7268, 862);
+------+--------------------+-------------------+----------------------------+---------------------+---------------------+------------------+
| id | student_first_name | student_last_name | email
| application_date | modification_date | unique_key |
+------+--------------------+-------------------+----------------------------+---------------------+---------------------+------------------+
| 862 | Phil | Powell | phil@blah.com |
2006-02-27 00:00:00 | 2006-02-27 00:00:00 | dF0WByrCP0vACftA |
| 7268 | Phil | Powell | phil@blah.com |
2006-02-27 00:00:00 | 2006-02-27 00:00:00 | dF0WByrCP0vACftA |
+------+--------------------+-------------------+----------------------------+---------------------+---------------------+------------------+

I accidentally created duplicate records upon attempting to migrate
data from one server to another. You will have 2, 3 or more records
with every single field identical except for the ID. Best way to know
they're dups is by "application_date" along with "unique_key".

Select Distinct Records
I am trying to compare the date part of a datetime value field with today's date....

Here's the sql:

mySQL = "Select * from Test WHERE TheDate LIKE'"&date()"' ORDER BY TheDate"
Set rs= Con.Execute( mySQL )

That return nothing even though Test has records for today..

Stop Duplicate Records
How do i stop duplicate records from being inserted into the database. i need to stop duplicates of title and link from being entered .....

Eliminate ALL Duplicate Records
I am trying to compare 1 field table 1.Mat_Num and compare it to table 2.Mat_Num
and eliminate all overlap so that only fields in table 1.Mat_Num that are not in table 2.Mat_Num are remaining. Or something like the following query:

Select table1.Mat_Num
From table1.Mat_Num, table2.Mat_Num
Where table1.Mat_Num <> table2.Mat_Num

I have used LIKE, NOT LIKE, IN, NOT IN, JOIN, and everthing else found on google just short of scripting. Obvously, my current results have been a loop listing every instance where table1.Mat_Num(Field X) NOT table2.Mat_Num(Field X). so it is not taking table1.Mat_Num(Field 1) and then searching all fields in table2.Mat_Num for an occurance.

How To Avoid Duplicate Records
I need to filter out duplicates for every 30 seconds. say i have two duplicate records within the 30 seconds limit. I need to show up only one. If there are identical records but with a different time settings(say above 30 seconds) then i need to display it. I need to restrict duplicate records within 30 seconds.

Picking Out Duplicate Records
I have a student results table with the layout shown below (four records with the fields separated by dashes). Sorry its so messy. Anyway, you can see that there are duplicates. I want to write an SQL statement that will pick out only the 'supplemental' records if duplicates occur. Any ideas on how to do this?

ID - StudentNo - Subject - Term - Yearofstudy - YearTaken - Grade
1195 - 11111111 - MA1E2 - Annual - 1 - 2006 - 34
1205 - 11111111 - MA1E2 - Supplemental - 1 - 2006 - 40 (S)
1194 - 11111111 - MA1E1 - Annual -1 - 2006 - 35
1204 - 11111111 - MA1E1 - Supplemental - 1 - 2006 - 40 (S)

Here is the SQL I'm using to get all the records from the studentresults table, for first years only:

SELECT *
FROM studentresults
WHERE studentresults.StudentNo = 11111111 AND studentresults.YearOfStudy = 1

How do I change this to pick out only the supplemental exam results?

Finding Duplicate Records
I'm trying to setup a way to find possible duplicate or near-duplicate
records of contact people. I have a table of 50k records doing an
inner join with itself, something like this:

SELECT p1.fields, p2.fields
FROM table AS p1 INNER JOIN table AS p2 ON p1.lastname = p2.lastname
WHERE p1.id <p2.id AND
(other criteria)

The id and name and other criteria fields are indexed as well as they
can be. It works fine as long as I add a LIMIT statement to keep it
down to just 10 or 20 records, but if I take this off, it runs on
forever -- so I can't even tell how many potentail records there will
be. If the table grows to 100k or 200k, it'll only get worse.

So my question is -- are there any better ways to go about this?

Duplicate Records Check
Could any one advise what SQL statement I would need to use, to check a
table for any duplicate records e.g that contain the same data within a
field. Note that I haven't got the field as a unqiue field.

Delete Duplicate Records
I have a sql script that finds the duplicates in a file. Now how do I go about getting rid of the duplicate entries without doing it manually?

Deleting Duplicate Records
how to delete duplicate records from my DB, where I have about 65502 records. I have a auto-incremend "id" field, then I have a file_letter field (which holds info like A, B, C - Z), and then I have a file_no field. Basically, the file_letter and file_no fields make a unique pair, thus there can be more than one "file_no = '23' (for ex. file_letter = A, file_no = 32 / file_letter=B, file_no = 32, etc etc)

How do I delete duplicate records using these two fields as a unique pair? I'm running MySQL 5.0.15-max-log.

Need To Delete Duplicate Records.
I used LOAD DATA FILE to load data into a mysql database. I discovered that
there are lots of duplicate records. Can anyone give me a simple list of
mysql commands to search and destroy duplicate records?

Way To Duplicate Table And Its Records?
Is there a way in mySQL to duplicate a table? I want to copy a table's structure and all of its records, for testing purposes.

Stop Duplicate Records
Sorry if this is extremely newbie of me but how do i stop duplicate records? I have created a small site with php and mysql which allows a user to reserve a parking space online.

Im only learning mysql and php so its all done very simply - I have a table with 2 fields - space and daybooked. users can go online and book for example Space number 2 for Wednesday. How would i carry out a check that would stop another user from double booking the same space?

Removing Duplicate Records
I received a table from a third party that should be primary keyed on a column called item_id. But that column has four rows that are duplicated, including item_id. I would like to remove one of each of the duplicate rows, leaving the other row intact.
MYSQLBrowser won't let me create a primary key until this is solved.

Non Duplicate Records Querry
I have a table with 701 duplicate records. The number of non duplicate records from this table is 179 I tryed to make a querry like :

Select Distinct count(recordname) from table

but the result is the same 701

What is the proper querry to get the 179 result ?

Remove Duplicate Records
What is the best way to remove duplicate records

Delete Duplicate Records.
I used LOAD DATA FILE to load data into a mysql database. I discovered that
there are lots of duplicate records. Can anyone give me a simple list of
mysql commands to search and destroy duplicate records

Stopping Duplicate Records
I've got a table which has three colums. The first is the row id, the other two are basically parent and child.
I need to ensure that there aren't any duplicate parent/child inserts, but without an extra hit to the DB to check first.

Distinct Records Ordered By Date
I have the basis of a forum similar in workings to this one. I am using perl with MySQL the problem i have is a simple SQL one.

I have a table storing all post data including fields.
forumID,
threadID,
postID,
postTitle,
datePosted,
timePosted

I want to list the threads in the order of last posted/replied to. So similar to this you click on the forum you want view and you get a list of the threads in that forum with the most recently active at the top. I don't mind how much data i can get for each thread, i can handle just getting the threadID and then i can loop back through it again and get the specific data for that thread (infact this would be preferable as it would give me more control although would work the server alittle harder).

the closest SQL i have to working is:

SELECT DISTINCT threadID FROM postdata WHERE forumID='$fid' ORDER BY dataPosted, timePosted DESC;

but the order seems not to change.

Returning Results Without Duplicate Records
I have 2 tables with related data.
one table is for products. and the other orders. In orders table one product may appear several times.
Now if i wanted to return an array with the products being ordered I will get duplicates of each product.
How can I return the results showing the products ordered wirhout containg duplicate rows

Avoid Duplicate Records In Within 30 Seconds
I'm working with php. I have a auction site, more or less. I want to create all-time rankings. The idea is to display where a seller ranks (all time) in the number of sales.

So for example, I'd display
John Doe All Time Sales Ranking: #138

I'm not exactly sure how to go about this.

$query = "SELECT count(*) as counter, SellerName FROM sales GROUP BY Sellername ORDER BY counter DESC";

This query would give me the data to list all of the Sellers in desc order by the number of sales.

In php, I could probably count until the Sellername was equal to $Sellername (already defined in their profile page), but I was hoping there would be a way to do this entirely in MySQL.

Duplicate Records Delete Query
I have a table with duplicate records but the key of the duplicate records is different, that is:

The key of one of the record has the format:

Rssss

The key of the second record has the format:

ssss

How can I delete both records from the table?

Selecting Top Records
I was posed a question in an interview that required me to select the top 3 records of table after doing a sort. I have been studying MySQL for the last 6 months or so and don't recall that capability in MySQL. I know it is available in Oracle and SQL Server. Is this possible in MySQL?

Selecting Many Different Records
I have table A_1 which has one of its fields called ID (a unique integer field). Now if I wanted to select let say the records with the ID matching these numbers 5, 7, 8, 9, 15, 22, 26, 29 how would I do this? I would imagine there is a better way to do this than using many AND statements to do this?

Selecting Known Records
Say I have a big list of IDs of the records I want to select.
What is the best way to do this?

1. A select statement with multiple OR statements
eg. SELECT * FROM table WHERE ID = 2 OR ID = 5 OR ID = 10 etc...

2. Multiple separate mysql queries each selecting an ID each.

3. A select statements like obove but all the queries sent in a single query
using union.

I dont know how important it is but with lots of queries a second it could
make a difference. If there is other better way please tell.

SELECTing Records
I want to select records for several events in the future. Here's my problem - I need the next seven days that events occur on - but these days are not nessicarily consecutive.

I had the idea to write a query like this:

SELECT
PROGRAMME.PROG_TITLE,
COMPANY.CO_NAME,
VENUE.VEN_CITY,
PERFORMANCE.PERF_DATE_TIME,
COUNT(DISTINCT DATE_FORMAT(PERFORMANCE.PERF_DATE_TIME,'%D-%m-%Y')) AS NUM_DAYS
FROM
PROGRAMME
JOIN COMPANY ON COMPANY.CO_ID = PROGRAMME.CO_ID
JOIN PERFORMANCE ON PERFORMANCE.PROG_ID = PROGRAMME.PROG_ID
JOIN VENUE ON VENUE.VEN_ID = PERFORMANCE.VEN_ID
WHERE NUM_DAYS <= 7
ORDER BY COMPANY.CO_NAME, PROG_DATE_TIME

Only to realize that I can't use a column alias in the WHERE clause. I know that there must be an easy way to do this but I can't seem to figure out how.

Selecting All Records
How would I go about selecting all records in a table with a date field of "date" -- "0000-00-00", "yyyy-mm-dd" -- that are 3 days older then the current date?

Selecting Records
I have one table containing itmes each having its own id, and a another table containing some of these numbers in one of its columns.I need to to build a query that returns the itmes from the first tables whose numbers are not present in the second table.

(SELECT t1.* FROM t1, t2 WHERE < the value in t1.item_id is nowhwere in t2.item_id >)

How do I do that?

Selecting Records
This is a bit of a forlorn hope, but are there any timestamp values automatically associated with records eg can I select all records created after a certain date

Selecting Records
my sql is not too hot so i hope someone can help me. I need to select all the records from one table that do not exist in 2 other tables. I know it sounds simple enough but for some reason i can not get it working. It may have something to do with the fact that the field i am searching on are datetime fields. Here is a shortened version of my code.
Code:

SELECT DateOfStats
FROM table1
WHERE (DateOfStats NOT IN
(SELECT dateofstats
FROM table2)) and (DateOfStats NOT IN
(SELECT dateofstats
FROM table3)

Selecting Non Matching Records
How do I go about selecting records from two tables that do not match?

example: I have two tables (Table1 and Table2) linked by field1. Table1 will have records added right away where as Table2 will get its records written at a later date. Thus having it in some cases where Table2 will not have a matching record for Table1.

What I want to do is generate a list of those items in Table1 that do not have a record written in Table2 as yet.

Selecting Specific Records
I have two tables. I want to select the last record for a specific type from one table and look up info from the other table. Is there a command that will select go to the last record of a query?

Selecting Records Only X Days Old
Using PERL and MySQL.

I have the select statement working for all records, is it possible to select only records (in this case it will only return one) that is as old as say 30 days?

Selecting Date Records
What is the most efficient way to select records based on a field called NOW
which contains a date or a datetimestamp

I use:
SELECT * FROM `TBL` WHERE
DATE_ADD( `TBL`.`NOW` , INTERVAL 3 MONTH ) > CURDATE( );

Is this the best way?

TIA
[color=blue]
> Nicolaas[/color]