Sorting method.. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Sorting method..

Author

Topic

TazMania
Yak Posting Veteran

63 Posts

Posted - 2007-04-13 : 04:31:23

Hi,

I'm looking for a way to do a sorting method..
This problem has properly been answered before, if so i'm very sorry for reposting.


"SELECT AdhocITSQL7.dbo.Medarbejder.Initialer,
AdhocITSQL7.dbo.Medarbejder.Navn,
AdhocITSQL7.dbo.Medarbejder.Afdeling,
AdhocITSQL7.dbo.Medarbejder.Telefon,
AdhocITSQL7.dbo.Medarbejder.Mobiltelefon,
AdhocITSQL7.dbo.Medarbejder.Lokation FROM Medarbejder 
WHERE 
(DataLength(AdhocITSQL7.dbo.Medarbejder.Initialer) <= 3 AND 
AdhocITSQL7.dbo.Medarbejder.Initialer LIKE '%"& Search &"%') OR (AdhocITSQL7.dbo.Medarbejder.Navn <> '' AND 
AdhocITSQL7.dbo.Medarbejder.Navn LIKE '%"& Search &"%') OR (AdhocITSQL7.dbo.Medarbejder.Telefon <> '' AND 
AdhocITSQL7.dbo.Medarbejder.Telefon LIKE '%"& Search &"%') OR (AdhocITSQL7.dbo.Medarbejder.Mobiltelefon <> '' AND 
AdhocITSQL7.dbo.Medarbejder.Mobiltelefon LIKE '%"& Search &"%') OR (AdhocITSQL7.dbo.Medarbejder.Lokation <> '' AND 
AdhocITSQL7.dbo.Medarbejder.Lokation LIKE '%"& Search &"%') 
ORDER BY AdhocITSQL7.dbo.Medarbejder.Lokation ASC,AdhocITSQL7.dbo.Medarbejder.Navn ASC"

The Search variabel are collected from a html form and passed on to asp classic.

The above sql string, are used for an internal phonebook, i'm looking for a way to group search based on Lokation ( Location ) and order by medarbejder ( employee name ) to give you an idea of the result i'm looking for, i've posted an example below.


Denmark
Jack Russell - 456 - 45678901
Peter Petersen - 123 - 12345678
England
Anders Jensen - 789 - 12345678
Sweden
Jan Mortensen - 123 - 12345678
Torvald Nielson - 901 - 56789012

Is the above example possible with sql ?
If so can you please give me a hint or two on how it's possible ?

Best regards
Taz

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-13 : 04:33:28

Can you do this at your front end application ? Why do you need to do this in SQL ?

TazMania
Yak Posting Veteran

63 Posts

Posted - 2007-04-13 : 04:38:48

hi khtan,

That's what i'm currently doing, with a select case and a while loop. but if the location name are changed, i need to adjust the frontend.

That's why I was thinking of a way to make this more dynamic.

Best regards
Taz

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-13 : 06:06:12

Post your question at ASP related Forum

Madhivanan

Failing to plan is Planning to fail

TazMania
Yak Posting Veteran

63 Posts

Posted - 2007-04-13 : 07:31:43

oki.. it seems like i can't be done in sql.. thanks for your time.

Best regards
Taz

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-13 : 08:25:04

It can be done, but the proper was to do it is in the front-end appliction.

See this post
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=80572

Peter Larsson
Helsingborg, Sweden

TazMania
Yak Posting Veteran

63 Posts

Posted - 2007-04-13 : 08:41:45

Thanks alot Peter,

Just what I needed.

Best regards
Taz

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-13 : 08:54:43

And if you don't need the extra empty lines, this is a shorter way to do it

SELECT		COALESCE(Person, UPPER(FavColor))
FROM		@Sample
GROUP BY	FavColor,
		Person
WITH ROLLUP
HAVING		FavColor IS NOT NULL
ORDER BY	FavColor,
		Person

Peter Larsson
Helsingborg, Sweden

TazMania
Yak Posting Veteran

63 Posts

Posted - 2007-04-13 : 09:07:48

hehe just great

thank you so much.. have a nice weekend.

Best regards
Taz

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-13 : 09:13:37

Du med!
Jag ska in till Axel Torv på lördag och ta en öl och äta rödspätta.

Cheers!

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-13 : 12:35:29

quote:
Originally posted by Peso

Du med!
Jag ska in till Axel Torv på lördag och ta en öl och äta rödspätta.

Cheers!

Peter Larsson
Helsingborg, Sweden

I need translation

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-13 : 13:33:10

Also: USE PARAMETERS! Never concatenate your sql command and variables together like that.

Please see: http://weblogs.sqlteam.com/jeffs/archive/2006/07/21/10728.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-13 : 23:15:30

quote:
Originally posted by madhivanan

quote:
Originally posted by Peso

Du med!
Jag ska in till Axel Torv på lördag och ta en öl och äta rödspätta.

Cheers!

Peter Larsson
Helsingborg, Sweden
I need translation

Madhivanan

Failing to plan is Planning to fail

I can translate this

Cheers!

means "B E E R" and lots of "B E E R"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources