useing the SELECT LEFT(table.column) syntax...

[suttercain]

Hi Everyone,

 

I currently have data that looks like this:

 

DE-01-001-02

DE-01-001-03

DE-01-002-01

DE-01-002-02

 

All I care about are the first 9 chracters, which would drop the remaining three. So basically instead of getting for results I would get the following two:

 

DE-01-001

DE-02-002

 

I tried the following sequel syntax and so so far I still get the entire data string:

 

$sql = mysql_query("SELECT *, LEFT(eo.executive_order,9) FROM device
				INNER JOIN device_eo ON (  device.device_id = device_eo.device_id )
				INNER JOIN eo ON ( device_eo.executive_order = eo.executive_order )
                                        WHERE eo.date_eo_rescinded IS NULL
				AND eo.level = '3'
				AND eo.url ='3'
				GROUP BY eo.executive_order
				ORDER BY device.device_mfr, eo.decs") 
				or die(mysql_error());

 

Can any let me know what I am doing wrong or an alternative to this method?

 

Thanks in advance.

 

-SC

[Barand]

SELECT * pulls all fields, including the unaltered fields. The LEFT bit will be in the final column of the query. Give it a column alias so you can reference it more easily.

[suttercain]

Awesome it worked! Thanks Barand!

[suttercain]

Okay, so Barand helped me big time with the previous [problem but now I am trying to take the script a step forward.

 

I have a column which houses thee following data:

 

DE-01-001-02

DE-01-001-03

DE-01-002-01

DE-01-002-02

 

I used this script:

 

<?php
$sql = mysql_query("
       SELECT *, LEFT(eo.executive_order,9) AS dog,
               FROM device
               INNER JOIN device_eo ON (  device.device_id = device_eo.device_id )
               INNER JOIN eo ON ( device_eo.executive_order = eo.executive_order )
               GROUP BY dog
               ORDER BY eo.date_eo_effective DESC
		   ")
               or die(mysql_error());
?>

 

Which gives me the following output instead of the 4 listed above.

 

DE-01-001

DE-01-002

 

Now I would like to list the newest "extension" pf that number so this:

 

DE-01-001-02

DE-01-001-03

DE-01-002-01

DE-01-002-02

 

Would become this

 

DE-01-001-03

DE-01-002-02

 

Group by the first 9 characters and echo the hightest value (the last two characters).

 

I tried this:

 

<?php
$sql = mysql_query("
		   SELECT *, LEFT(eo.executive_order,9) AS dog,      MID(eo.executive_order,11,2) AS cat FROM device
               INNER JOIN device_eo ON (  device.device_id = device_eo.device_id )
               INNER JOIN eo ON ( device_eo.executive_order = eo.executive_order )
               GROUP BY dog
               ORDER BY eo.date_eo_effective DESC
		   ")
               or die(mysql_error());
?>

 

But did not get the desired result.

 

Thank in advance.

[Barand]

SELECT SUBSTRING_INDEX(codenum,'-',3) as partcode,

MAX(codenum) FROM product

GROUP BY partcode;

[Barand]

Are you doing this so you can generate the next unique key?