CODEWITHSUNDEEP
sqlsql-servert-sql
Asynchronous
Asynchronous

Reputation: 3977

Return string based on values within the string without truncating

I have a column containing values like this, example:

Farari - Made in 2013

Mercedes - Made in 2012

Jaguar - Made in 1978

I want to return the car with the highest or recent make year:

Something like this will give me the year but obviously will truncate the rest of the string:

SELECT
MAX(RIGHT(CarProfile, 4)) 
FROM mySchema.Car;

How do I get the highest year but maintain the full string?

In this case: Farari - Made in 2013

Upvotes: 1

Views: 63

Answers (4)

cameronjonesweb
cameronjonesweb

Reputation: 2506

You should get the 'made in' when you print it out, not stored in the database. So instead your table will be:

CarProfile | Year
---------------
Ferrari | 2013
Mercedes | 2012
Jaguar | 1978

And your 'made in' will either be retrieved via the query like so:

SELECT  CarProfile, ' - made in', year
FROM    Car
HAVING  year = MAX(year)

Or you can just write it when it is displayed (for example php):

<?php echo $CarProfile . " - made in " $year;?>

It will make your table much more compact and friendly

Upvotes: 0

sunysen
sunysen

Reputation: 2351

Sql below for reference:

select CarProfile
  from FROM mySchema.Car
 where substr(CarProfile, CarProfile.length - 4, CarProfile.length) =
        SELECT MAX(RIGHT(CarProfile, 4)) FROM mySchema.Car;

Upvotes: 0

Madhivanan
Madhivanan

Reputation: 13700

SELECT  top 1 *
FROM    Car order by RIGHT(CarProfile, 4)*1 desc

Upvotes: 2

John Woo
John Woo

Reputation: 263733

SELECT  *
FROM    Car
WHERE   RIGHT(CarProfile, 4) =
        (SELECT MAX(RIGHT(CarProfile, 4)) 
        FROM Car)

You should normalize your table properly. My ssugested schema would be adding a column for a year and have index it so that it would have great query performance .

Upvotes: 1

Related Questions