Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Order By

Posted on 2006-10-31
27
Medium Priority
?
524 Views
Last Modified: 2008-02-01
I am running a select statement to pull values out of a database.  I want to order [Mall_ID] and then by a specific [company name] and then have the rest order by asc.  How can I do this.  As an example I have the following stores:

Walmart
Sears
Kmart
Walgreens

I want to sort by Sears First and then the rest by asc order.  Sears always being on top if it is an option.  So it would look like this:

Sears
Kmart
Walgreens
Walmart

I am using this to test it in Access and it works but VBScript I have read does not like the iif statement.  How else can I do this?  I need this in an select SQL type statement.


0
Comment
Question by:flfmmqp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 8
  • 5
  • +1
27 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17842681
somthing like this:
order by Mall_ID, case when [company name] = 'ABC' then 0 else 1 end, [company name]
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17842683
order by Mall_ID, case when [company name] = 'Sears' then 0 else 1 end, [company name]
0
 

Author Comment

by:flfmmqp
ID: 17842690
Sorry forget to add the SQL statement I currently have:

SELECT Mallsgis_Labels.Mall_ID, Mallsgis_Labels.[Mall Name], Mallsgis_Labels.[Company Name]
FROM Mallsgis_Labels
ORDER BY Mallsgis_Labels.Mall_ID, IIf(Mallsgis_Labels.[company name]="Sears"," " & Mallsgis_Labels.[company name], Mallsgis_Labels.[company name]);
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 96

Expert Comment

by:Lee W, MVP
ID: 17842695
Have you tried

... ORDER BY Mall_ID, Company_Name
0
 

Author Comment

by:flfmmqp
ID: 17842700
I don't completely understand AngelIII  could you edit my example SQL?
0
 
LVL 6

Expert Comment

by:yhwhlivesinme
ID: 17842713
you can also do it this way:

SELECT Mallsgis_Labels.Mall_ID, Mallsgis_Labels.[Mall Name], Mallsgis_Labels.[Company Name]
FROM Mallsgis_Labels
Where Mallsgis_Labels.[Company Name] <> "Sears"

Union

SELECT Mallsgis_Labels.Mall_ID, Mallsgis_Labels.[Mall Name], " " & Mallsgis_Labels.[Company Name]
FROM Mallsgis_Labels
Where Mallsgis_Labels.[Company Name] = "Sears"
ORDER BY 2, 3
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17842723
yes, like this:

SELECT Mallsgis_Labels.Mall_ID, Mallsgis_Labels.[Mall Name], Mallsgis_Labels.[Company Name]
FROM Mallsgis_Labels
ORDER BY Mallsgis_Labels.Mall_ID, IIf(Mallsgis_Labels.[company name]="Sears",0,1), Mallsgis_Labels.[company name];

I actually read too late that it was about access...
0
 

Author Comment

by:flfmmqp
ID: 17842728
leew

I first want to order by Mall_ID.  Then I want to order where Company_Name = 'Sears' and then order by Company_Name in Asc.

So the what you have takes care of two of the three problems except where company_name = 'Sears'
0
 

Author Comment

by:flfmmqp
ID: 17842736
At first it was in Access so I could see the results but then I moved to VB Script and found out it does not like iif statements.  So in the end it is VB Script.
0
 
LVL 6

Expert Comment

by:yhwhlivesinme
ID: 17842759
This would actually work better, as you'd have a workable column without the space in front :)

SELECT Mallsgis_Labels.Mall_ID, Mallsgis_Labels.[Mall Name], Mallsgis_Labels.[Company Name] as companyname, Mallsgis_Labels.[Company Name] as Sortcolumn
FROM Mallsgis_Labels
Where Mallsgis_Labels.[Company Name] <> "Sears"

Union

SELECT Mallsgis_Labels.Mall_ID, Mallsgis_Labels.[Mall Name],Mallsgis_Labels.[Company Name], " " & Mallsgis_Labels.[Company Name]
FROM Mallsgis_Labels
Where Mallsgis_Labels.[Company Name] = "Sears"
ORDER BY 1, 4
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17842761
how do you connect to the access database?
0
 

Author Comment

by:flfmmqp
ID: 17842774
yhwhlivesinme

Well that works in Access.  I am off to see if I can get it to work in VBScript.
0
 
LVL 6

Expert Comment

by:yhwhlivesinme
ID: 17842782
:-D iif is an access function, union you should be able to use in any sql situation (as far as I know, but I'm more of an access guy)
0
 

Author Comment

by:flfmmqp
ID: 17842799
Set ADOConn = CreateObject("ADODB.Connection")
Set rsLbls = CreateObject("ADODB.Recordset")
ADOConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=R:\Loc Database\Locational.mdb;"
rsLbls.Open strLblQry, ADOConn, 3, 1, 1
0
 

Author Comment

by:flfmmqp
ID: 17842818
Augh.  It is not working in the VB script.  Does VBScript not like Union as well.  It seems to really dislike the  (" " & Mallsgis_Labels) part for both the iif statement and the Union statement.  Any other ideas?  Come on yhwhlivesinme I know you have another way to do this query!  You are an Query writing machine.  An unstoppable machine that will not take no for an answer.
0
 

Author Comment

by:flfmmqp
ID: 17842835
Out of curiosity what does yhwhlivesinme stand for ro mean?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17842869
this should work:

strLblQry = "SELECT Mallsgis_Labels.Mall_ID, Mallsgis_Labels.[Mall Name], Mallsgis_Labels.[Company Name] FROM Mallsgis_Labels ORDER BY Mallsgis_Labels.Mall_ID, IIf(Mallsgis_Labels.[company name]=""Sears"",0,1), Mallsgis_Labels.[company name] "
0
 
LVL 6

Expert Comment

by:yhwhlivesinme
ID: 17842871
haha lol @ query writing machine :)

Can you give me all of your code? you might have to replace that with single quotes or something, I'm assuming somewhere you're doing:

dim sql as string
sql = "Select...." etc.
0
 
LVL 6

Expert Comment

by:yhwhlivesinme
ID: 17842893
angel may be correct try that before you try the union, union queries aren't the best to use because they take forever on large sets of data, because you're querying the same set of data twice.

yhwh stands for yahweh (hebrew word for God) the Israelites thought it too holy to write the name of god so they removed the vowels when they wrote it.  the rest is: "lives in me"
0
 

Author Comment

by:flfmmqp
ID: 17842993
AngelIII, As I stated earlier VBScript will not use the IIF statement.  Thanks for helping though.  Any other ideas?


Currently my code looks like this yhwhlivesinme

Dim strLabel, strLblQry, ADOConn, rsLbls, iCount, i
strLblQry = "SELECT Mallsgis_Labels.Mall_ID, Mallsgis_Labels.[Mall Name], Mallsgis_Labels.[Company Name] FROM Mallsgis_Labels ORDER BY Mallsgis_Labels.Mall_ID, IIf(Mallsgis_Labels.[company name]=""Sears"",0,1), Mallsgis_Labels.[company name] "
Set ADOConn = CreateObject("ADODB.Connection")
Set rsLbls = CreateObject("ADODB.Recordset")
ADOConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=R:\Loc Database\Locational.mdb;"
rsLbls.Open strLblQry, ADOConn, 3, 1, 1
iCount =rsLbls.Fields.Item("AnchorCount").Value
rsLbls.MoveFirst


I am trying to change the strLblQry line to this:

strLblQry ="SELECT Mallsgis_Labels.Mall_ID, Mallsgis_Labels.[Mall Name], Mallsgis_Labels.[Company Name] FROM Mallsgis_Labels Where Mallsgis_Labels.[Company Name] <> 'Sears'  UNION SELECT Mallsgis_Labels.Mall_ID, Mallsgis_Labels.[Mall Name], " " & Mallsgis_Labels.[Company Name] FROM Mallsgis_Labels Where Mallsgis_Labels.[Company Name] = 'Sears' ORDER BY 2, 3;"
0
 

Author Comment

by:flfmmqp
ID: 17843017
I am always amazed at the reasons behind peoples user names.  Very cool name.  Sounds a lot like Harry Potter stole "he who must not be named" from the Israelites.  (Only funny if you know anything about Harry Potter)
0
 
LVL 6

Accepted Solution

by:
yhwhlivesinme earned 2000 total points
ID: 17843059
I love Harry Potter yes it does sound a lot like that haha!

as far as the query goes, try this:

strLblQry = "SELECT Mallsgis_Labels.Mall_ID, Mallsgis_Labels.[Mall Name], Mallsgis_Labels.[Company Name] FROM Mallsgis_Labels Where Mallsgis_Labels.[Company Name] <> 'Sears'  UNION SELECT Mallsgis_Labels.Mall_ID, Mallsgis_Labels.[Mall Name], ' ' & Mallsgis_Labels.[Company Name] FROM Mallsgis_Labels Where Mallsgis_Labels.[Company Name] = 'Sears' ORDER BY 1, 3;"

using the single quotes should help, and you need to make sure the ORDER BY is 1, 3 ...not 2, 3
0
 

Author Comment

by:flfmmqp
ID: 17843241
This may work I will need some time.  Going to lunch will try when I get back.  Thanks for all of the help.
0
 

Author Comment

by:flfmmqp
ID: 17844573
Okay this is where I am:

strLblQry = ""SELECT Mallsgis_Labels.Mall_ID, Mallsgis_Labels.[Mall Name], Mallsgis_Labels.[Company Name], Mallsgis_Labels.[AnchorCount], Mallsgis_Labels.[Division Name], Mallsgis_Labels.[Gross_SF] FROM Mallsgis_Labels Where Mallsgis_Labels.[Company Name] <> 'Federated Department Stores, Inc'  UNION SELECT Mallsgis_Labels.Mall_ID, Mallsgis_Labels.[Mall Name], ' ' & Mallsgis_Labels.[Company Name], Mallsgis_Labels.[AnchorCount], Mallsgis_Labels.[Division Name], Mallsgis_Labels.[Gross_SF]  FROM Mallsgis_Labels Where Mallsgis_Labels.Mall_ID = "" & [Mall_ID], Mallsgis_Labels.[Company Name] = 'Federated Department Stores, Inc' ORDER BY 1, 3


I need to add one more thing.  I need only where Mallsgis_Labels.Mall_ID =4.   I'm sooo close to being done.  Thanks for your help.
0
 
LVL 6

Expert Comment

by:yhwhlivesinme
ID: 17844625
Try this....I wasn't sure why you had Mall_ID in there, it looked like you were trying to get value from a recordset or something....take a look at what I got, you need to make sure you put the Mallsgis_Labels.Mall_ID = 4 in both queries, the first and the second (with a union it's putting together two seperate queries)

strLblQry = "SELECT Mallsgis_Labels.Mall_ID, Mallsgis_Labels.[Mall Name], Mallsgis_Labels.[Company Name], Mallsgis_Labels.[AnchorCount], Mallsgis_Labels.[Division Name], Mallsgis_Labels.[Gross_SF] FROM Mallsgis_Labels Where Mallsgis_Labels.Mall_ID = 4 and Mallsgis_Labels.[Company Name] <> 'Federated Department Stores, Inc' UNION SELECT Mallsgis_Labels.Mall_ID, Mallsgis_Labels.[Mall Name], ' ' & Mallsgis_Labels.[Company Name], Mallsgis_Labels.[AnchorCount], Mallsgis_Labels.[Division Name], Mallsgis_Labels.[Gross_SF] FROM Mallsgis_Labels Where Mallsgis_Labels.Mall_ID = 4 and Mallsgis_Labels.[Company Name] = 'Federated Department Stores, Inc' ORDER BY 1, 3"
0
 

Author Comment

by:flfmmqp
ID: 17844975
Thanks for all of the help.  
0
 
LVL 6

Expert Comment

by:yhwhlivesinme
ID: 17844983
No problem did you get it working?
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question