flfmmqp
asked on
Order By
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.
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.
order by Mall_ID, case when [company name] = 'Sears' then 0 else 1 end, [company name]
ASKER
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.[compa ny name]="Sears"," " & Mallsgis_Labels.[company name], Mallsgis_Labels.[company name]);
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.[compa
Have you tried
... ORDER BY Mall_ID, Company_Name
... ORDER BY Mall_ID, Company_Name
ASKER
I don't completely understand AngelIII could you edit my example SQL?
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
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
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.[compa ny name]="Sears",0,1), Mallsgis_Labels.[company name];
I actually read too late that it was about access...
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.[compa
I actually read too late that it was about access...
ASKER
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'
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'
ASKER
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.
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.[Com pany Name], " " & Mallsgis_Labels.[Company Name]
FROM Mallsgis_Labels
Where Mallsgis_Labels.[Company Name] = "Sears"
ORDER BY 1, 4
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.[Com
FROM Mallsgis_Labels
Where Mallsgis_Labels.[Company Name] = "Sears"
ORDER BY 1, 4
how do you connect to the access database?
ASKER
yhwhlivesinme
Well that works in Access. I am off to see if I can get it to work in VBScript.
Well that works in Access. I am off to see if I can get it to work in VBScript.
:-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)
ASKER
Set ADOConn = CreateObject("ADODB.Connec tion")
Set rsLbls = CreateObject("ADODB.Record set")
ADOConn.Open "Provider=Microsoft.Jet.OL EDB.4.0; Data Source=R:\Loc Database\Locational.mdb;"
rsLbls.Open strLblQry, ADOConn, 3, 1, 1
Set rsLbls = CreateObject("ADODB.Record
ADOConn.Open "Provider=Microsoft.Jet.OL
rsLbls.Open strLblQry, ADOConn, 3, 1, 1
ASKER
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.
ASKER
Out of curiosity what does yhwhlivesinme stand for ro mean?
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.[compa ny name]=""Sears"",0,1), Mallsgis_Labels.[company name] "
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.[compa
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.
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.
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"
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"
ASKER
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.[compa ny name]=""Sears"",0,1), Mallsgis_Labels.[company name] "
Set ADOConn = CreateObject("ADODB.Connec tion")
Set rsLbls = CreateObject("ADODB.Record set")
ADOConn.Open "Provider=Microsoft.Jet.OL EDB.4.0; Data Source=R:\Loc Database\Locational.mdb;"
rsLbls.Open strLblQry, ADOConn, 3, 1, 1
iCount =rsLbls.Fields.Item("Ancho rCount").V alue
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;"
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.[compa
Set ADOConn = CreateObject("ADODB.Connec
Set rsLbls = CreateObject("ADODB.Record
ADOConn.Open "Provider=Microsoft.Jet.OL
rsLbls.Open strLblQry, ADOConn, 3, 1, 1
iCount =rsLbls.Fields.Item("Ancho
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;"
ASKER
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This may work I will need some time. Going to lunch will try when I get back. Thanks for all of the help.
ASKER
Okay this is where I am:
strLblQry = ""SELECT Mallsgis_Labels.Mall_ID, Mallsgis_Labels.[Mall Name], Mallsgis_Labels.[Company Name], Mallsgis_Labels.[AnchorCou nt], 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.[AnchorCou nt], 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.
strLblQry = ""SELECT Mallsgis_Labels.Mall_ID, Mallsgis_Labels.[Mall Name], Mallsgis_Labels.[Company Name], Mallsgis_Labels.[AnchorCou
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.
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.[AnchorCou nt], 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.[AnchorCou nt], 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"
strLblQry = "SELECT Mallsgis_Labels.Mall_ID, Mallsgis_Labels.[Mall Name], Mallsgis_Labels.[Company Name], Mallsgis_Labels.[AnchorCou
ASKER
Thanks for all of the help.
No problem did you get it working?
order by Mall_ID, case when [company name] = 'ABC' then 0 else 1 end, [company name]