Sort on string 5

[TRACEYMARYLAND]

Hi there
i have the following

FSSERIES.12.3.2004
FSSERIES.12.14.2004
INS.12.5.2004
INS.12.7.2004

where it is form name plus a date

So i need to get the max record for each of the Forms so i would only see
FSSERIES.12.14.2004 and
INS.12.7.2004

Any suggestions

 

[donutman]

Stop placing multiple pieces of data into one column.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

Ok, now I'm pissed. I've been giving a few really good answers the past 3 days and haven't earned a single star! Now, I've been a bit sarcastic and someone gave me one? Sometimes life just isn't fair.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[WhiteKnight57t]

Donut


You Star you

==========================
Date is a way to show you care
Time is a great healer
DateTime is just damn confusing

Tim

 

[chrissie1]

Donutman,

Blame 'I can't ...'

and a fifth from me.

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[donutman]

Pity votes...guess if I complained about that, I'd be a really big sap...pretty much the same if I thanked anyone. Say chrissie, ya notice that the admin 86ed our thread. Thought that was a little heavy handed...but with the same probable reasoning this one should go too. Unless it was because I said ...central European... ???
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[maswien]

try this:

select
left( thestring, charindex( '.',thestring, 1) - 1 ),
max(thestring)
from formdate
group by
left( thestring, charindex( '.',thestring, 1) - 1 )

 

[donutman]

Mija, that's a start, but you're going to have to make that a derived table and add a true date field unless you want to parse the month, day and year for a more accurate sort.
-Karl



[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[TRACEYMARYLAND]

Donutman......you never got a star im sorry.
Any advice i get is always appreciated.

Oh the
Stop placing multiple pieces of data into one column.

Is true but this is an ISO-9001 requirement.

Trust me i WOULD never do it like that..........iether

 

[donutman]

Tracey,
Please understand that here on Tek-Tips we see this sort of database design frequently. I've never heard that it was a requirement. Usually it goes, "I've inherited this system and I know it is for #x$%."
The solution to that sort of requirement is quite simple. Add additional columns that store the information properly. If the table cannot be altered because it would break an application or an ISO-9001 requirement, then create a helper table. Use the primary key of the main table to create a one-to-one table that contains the additional columns. Mija has given you the code to split off the Form name and the balance of the string would be the date which you could Cast as a datetime.
Do you need help with populating a table? BTW, it can be done without this helper table, but it will make your life simpler if you had it, although on the downside it will consume some additional resources to occasionally update it. I certainly owe you all the time it takes to get this job done. Which direction do you wish to take?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]