tricky sql query... - 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

Other Forums

MS Access

tricky sql query...

Author

Topic

rum23
Yak Posting Veteran

77 Posts

Posted - 2008-10-20 : 11:04:33

I basically display a list of projects in a tree control in ACCESS 2007 (SQL Server 2005 is the back end). Each node in the tree is linked to a project name. This project list can be pretty lenghty and the user can do a search on the project rather than going thro' the entire list. If the search finds any matching results, those project nodes in the tree control are highlighted in a different color. Now what I want is, after the search, the tree should refresh with the search results being on the top. Is this possible to do via an SQL query...

basically i want to do a Select * from....Order by Project name with a particular project being the first row. In other words, order by a particular project name.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-20 : 11:16:58

Select * from your_table
Order by iif(project_name='some name'1,2)

Madhivanan

Failing to plan is Planning to fail

rum23
Yak Posting Veteran

77 Posts

Posted - 2008-10-20 : 11:47:04

hmm...this doesn't seem to be working....The list is sorted some order but not in the order that I expected.

If the list is like this

Bob
Charlie
Dug
Greg
Harry
Nancy

and the user searches for "Harry", then i want to display the list like this

Harry --> this will be top of the list and highlighted.
Bob
Charlie
Dug
Greg
Nancy

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-21 : 03:23:30

Select * from your_table
Order by iif(project_name='some name'1,2),project_name

Madhivanan

Failing to plan is Planning to fail

rum23
Yak Posting Veteran

77 Posts

Posted - 2008-10-21 : 09:11:54

Select * from your_table Order by iif(project_name='some name'1,2),project_name

gives me a syntax error about the missing comma.

After adding the missing comma, the sql executes but I still don't see the data in the order that I want.
Select * from your_table
Order by iif(project_name='some name',1,2),project_name

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-21 : 09:18:25

You should replace some name with actual value

Madhivanan

Failing to plan is Planning to fail

rum23
Yak Posting Veteran

77 Posts

Posted - 2008-10-21 : 09:51:16

of course, I did that.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-21 : 09:58:41

quote:
Originally posted by rum23

of course, I did that.

Is the table case sensitive?

Try

select * from tt
order by iif(lcase(project_name)=lcase('Harry'),1,2),project_name

Madhivanan

Failing to plan is Planning to fail

rum23
Yak Posting Veteran

77 Posts

Posted - 2008-10-21 : 10:10:21

Prefect!

Looks like the tables are case-sensitive.

I'm almost there....why doesn't the sql below sort the data?

select * from tt
order by iif(lcase(project_name) LIKE lcase('Harr%'),1,2),project_name

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-21 : 10:16:13

select * from tt
order by iif(left(lcase(project_name),4) = lcase('Harr'),1,2),project_name

Madhivanan

Failing to plan is Planning to fail

rum23
Yak Posting Veteran

77 Posts

Posted - 2008-10-21 : 10:17:41

select * from tt
order by iif(lcase(project_name) LIKE lcase('Harr*'),1,2),project_name

This SQL works.

Thanks for your help.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-21 : 10:22:14

quote:
Originally posted by rum23

select * from tt
order by iif(lcase(project_name) LIKE lcase('Harr*'),1,2),project_name

This SQL works.

Thanks for your help.

Well. I forgot that we should use * instead of %

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources