Page 1 of 1

eliminate duplicates on query

Posted: Sat Apr 28, 2012 12:25 pm
by inosent1
i have used a query like this and it will show me a list of records

Code: Select all

$sql = mysql_query("select * from m_data where file_id = '$file_id' ORDER BY id DESC");
but now i want to run a query from a table that looks like this

file_id name item#
1 smith 1
1 smith 2
1 smith 3
1 smith 4

i want my result to be

1 smith

all i need is for the query to pull the first instance and show it on the list that follows.

how can i change the query to eliminate showing a duplicate entry?

Re: eliminate duplicates on query

Posted: Sat Apr 28, 2012 5:31 pm
by requinix
Throw in a DISTINCT.

Code: Select all

SELECT DISTINCT file_id, name FROM table
But I have a question: why is there so much duplicate data?

Re: eliminate duplicates on query

Posted: Thu May 03, 2012 10:30 am
by inosent1
thanks for the reply.

it is a table of assigned tasks related to on particular property. i want the user on one screen to see a list of props they need to work on and in a different area review tasks

Re: eliminate duplicates on query

Posted: Thu May 03, 2012 8:49 pm
by califdon
Without seeing your database schema (tables and columns), it is hard to give you any suggestions, but the reason requinix raised the question is that what you showed strongly hints that your database is unnormalized, which means that it is very inefficient. Just because you want to display data in some particular manner is NOT a reason to STORE the data that way. That's what you do in spreadsheets, but NOT in databases! This is a critical factor in being able to retrieve data with queries.

Re: eliminate duplicates on query

Posted: Sun May 06, 2012 5:00 am
by inosent1
this works:

Code: Select all

$query="SELECT * FROM assignments WHERE uid ='$row5[0]' AND dt_complete ='$btn' ORDER BY id DESC";

this doesnt:

Code: Select all

$query="SELECT DISTINCT file_id FROM assignments WHERE uid ='$row5[0]' AND dt_complete ='$btn' ORDER BY id DESC";

with the first one all the data related to the search shows up. but when i use the distinct i get

Code: Select all

Warning: mysql_result() [function.mysql-result]: b_fname not found in MySQL result index 9 in /home/content/20/8370920/html/f/acq_pipe.php on line 908


Re: eliminate duplicates on query

Posted: Mon May 07, 2012 8:46 am
by x_mutatis_mutandis_x

Code: Select all

$query="SELECT DISTINCT file_id, b_fname /*,column_2, column_3 etc.*/ FROM assignments WHERE uid ='$row5[0]' AND dt_complete ='$btn' ORDER BY id DESC";