Tilted Forum Project Discussion Community

Tilted Forum Project Discussion Community (https://thetfp.com/tfp/)
-   Tilted Technology (https://thetfp.com/tfp/tilted-technology/)
-   -   [SQL] ORDER BY question (https://thetfp.com/tfp/tilted-technology/52893-sql-order-question.html)

Moskie 04-19-2004 05:48 AM

[SQL] ORDER BY question
 
As part of an assignment, I've been asked to sort and display some information using different sort methods. One of the sort methods I'm assigned is to sort the information directly in the SQL statement. That normally would be extremely simply, but it seems particularly complicated in this situation.

I have a table called Employee, which has a column called "Name". Names in this column are in the form "<FirstName><space><LastName>". Such as "Joe Smith". The problem is that I am supposed to sort by Employee's last name, which means I can't just do "ORDER BY Name". Is there some kinky thing I can put in the ORDER BY clause to get this sorted properly?

It also crossed my mind that I could write a stored procedure that would produce a record's last name for me, but I'm not sure how to do that... my string-manipulation-within-SQL skills are not that great, and I'm not even sure if it can be done.

Any thoughts on how to do this?


edit: I should add that I'm doing this with an MBD file in a Visual Studio .Net 2003 VB application.

Fallon 04-19-2004 10:06 AM

Are you restricted to having the name all together? Or can you make a first/last name?
Sorry I got limited knowledge of SQL

cliche 04-19-2004 11:19 AM

Can you not ORDER BY SUBSTRING(Name FROM LOCATE(' ',Name)) or similar? (I'm aware that this includes the space).
I don't have an SQL set up to see if that would work but might be worth a try. Alternatively ORDER BY SUBSTRING(Name, LOCATE(' ',Name)).

KnifeMissile 04-19-2004 11:31 AM

Unfortunately, I don't know SQL well enough to answer your literal question (but cliche looks like he's onto something) but let me ask you an obvious question.

Is there a particular reason why first name and surname don't have their own columns? Usually, this is the case and, in this case, sorting by surname is easy.
Is this difficulty, in fact, part of the assignment?

Moskie 04-19-2004 01:02 PM

Thanks guys, I got a similar idea to cliche's working. Wasn't nearly as bad as I feared. ;)

And I do think that was the purpose of it KnifeMissile... at least I hope so. If he says I could have just split it up into two columns, i'll be pissed! :p

Latch 04-20-2004 01:14 AM

Just for general knowledge, what'd you end up with (that worked)?

Thanks.

Moskie 04-20-2004 05:18 AM

My solution was:

Code:

SELECT Name FROM Employee ORDER BY MID(Name,INSTR(Name, ' ')+1)
INSTR returns the index of the first space in Name. Using that index, I use MID to get everything after that location, which is the last name. ORDER BY that result, and you got it sorted.

I'm not sure how you'd do this on a different (non-MSAccess) database, but I think most databases have equivalent functions to use.

Cuball 04-20-2004 05:25 AM

if you normalized your table you wouldn't have to deal with that kind of problem


All times are GMT -8. The time now is 12:53 PM.

Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2025, vBulletin Solutions, Inc.
Search Engine Optimization by vBSEO 3.6.0 PL2
© 2002-2012 Tilted Forum Project


1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40