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";