[SOLVED] select distinct brings all the records from my table.

[sayedsohail]

Hi everyone,

 

using select distinct i heard got some performance issues for large databases and it was recommended to use where clause.

 

I created a table (service) which has got four fields for recording car service history, i.e, what actions has been taken for a single visit/service.

 

car_id

service_id

service_description

date

 

The records would be like

 

car_id service_id service_description date

1 a Oil change 01/01/2007

1 b Filter Change 01/01/2007

1 c x x x ""

2 a Oil .... ""

2 b Filter .... ""

1          b        Filter Change        02/02/2008 

 

My question is: How do i select only one record for each car i.e, only the first record

 

if i try this:

 

select distinct (car_id), service_description from service; It brings all the records from the table

 

Instead I want to display only one record for each car_id data wize i.e,

 

car_id service_id service_description date

1 a Oil change 01/01/2007

1 b Filter Change 02/02/2008

2 a Oil .... ""

 

 

any ideas?

 

[Illusion]

no ideas other than this

 

$sid='a';

 

$query="select car_id, service_description,date from service where service_id='$sid'";

 

 

[radalin]

well Illusion is right about that. But it will check service_id s not car_id s

for car ids you have to use: car_id instead of service_id

 

Distinct gets rows that are different, even if one field is different than it's distinct record and it must be fetched.

[gizmola]

There's a few different ways to accomplish this.  The first thing I noted is you used an unusual keying system where your service_id appears to be a letter.  This is a bad design for many reasons, not the least of which is that have to manually figure out the next visit using either ascii numbering or some hard coded alphabet system.  Your sample data also doesn't seem to make sense: for example you have the same car with two different visits on the same day.  Did you really have the same car on the same day with two different service visits?  Also do you really want the design to allow for only one thing to be done on one visit?  I'm guessing not.

 

If this is an academic exercist, then that is one thing, if it's a production system, then the problems with your design are more important to fix than the answer to this one query.

 

 

[sayedsohail]

First of all many thanks for bringing this knowledge to us, i correct the first issue and here is the revised version of my table:

 

id autoincrement

car_id int

service_id int

service_description varchar

s_date date

 

The records would be like without id since, this is an autoincrement field. Yes on a single visit, the car might have multiple works performed i.e., oil change, filter change, denting etc and on each different visit different things performed.

 

car_id service_id service_description date

1   1   Oil change   01/01/2007

1   2   Filter Change   01/01/2007

1   3   x x x ""

2   1   Oil .... ""

2   2   Filter ....   ""

1   2   Filter Change   02/02/2008

 

My question is: How do i select only one record for each visit i.e, only the first record

 

if i try this:

 

select distinct (car_id), service_description from service; It brings all the records from the table

 

Instead I wish to pull only one record for each visit i.e,

 

car_id service_id service_description date

1 1 Oil change 01/01/2007

1 2 Filter Change 02/02/2008

2 1 Oil change 01/01/2007

 

 

[Wildbug]

DISTINCT only returns entirely distinct rows, not a distinct column value.  You can use GROUP BY, however, to accomplish this.

 

SELECT car_id,MAX(date) FROM service GROUP BY car_id;

# Or if you want the latest row from each:

SELECT s1.* FROM service AS s1 JOIN (SELECT car_id,MAX(date) AS mdate FROM service GROUP BY car_id) AS s2 ON (s1.car_id=s2.car_id AND s1.date=s2.mdate)

[sayedsohail]

big thanks wil.