|
I have a table in my database called "country"
In it I have "country_id" and "country_name".
I want to write a query that outputs the countryname in order, but the first two would be out of order:
i.e.
UK
USA
Afghanistan
Albania
Algeria
American Somoa
Andora
...
Zambia
Zimbabwe
I know it's possible but stupidly just can't think how. I'd rather do this by SQL than on my ASP page as there will be a couple of places it will be called from.
Please help! 
|
|
|
|
|
Add an extra column to the table for ordering. For each group have an order number, e.g. UK and USA could be order number 0, everything else could be order number 1. Then in your SQL you can do an ORDER BY OrderNumber, CountryName.
Does this help?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
Aargh! You beat me by two minutes
I like the 0 and 1 idea. I was thinking 1, 2, 3, 4... but I thought that might take too long.
The man who smiles when things go wrong has thought of someone he can blame it on.
If you tell a man there are 300 billion stars in the universe, he'll believe you. But if you tell him a bench has just been painted, he'll have to touch it to be sure.
|
|
|
|
|
Yeah hadn't thought about doing it that way.
I remember somewhile ago using NULL values in the sql, to be able to play with it, but I've been hammering my head and just can't remember!
Cheers
|
|
|
|
|
You could add a "OrderRank" column where you give your own order (which may take a while with countries) and order by that or you could select the two countries into a table variable and then select the rest of the values except for the UK and US. Then just select everything out of the table variable.
The man who smiles when things go wrong has thought of someone he can blame it on.
If you tell a man there are 300 billion stars in the universe, he'll believe you. But if you tell him a bench has just been painted, he'll have to touch it to be sure.
-- modified at 7:14 Monday 10th October, 2005
|
|
|
|
|
Ok going to be really cheeky.. but what's the basic setup of code?
DECLARE @vCountryName varchar(30), @vCountryName2 varchar(30)
SET @vCountryName = "UK"
SET @vCountryName2 = "USA"
SELECT @vCountryName, @vCountryName2, CountryName
FROM Country
ORDER BY @vCountryName, @vCountryName2, CountryName
I am going to be needing to output it into a single result set.
|
|
|
|
|
A table variable is sort of like a tempory table only it doesn't have as much overhead as a temp table.
Here's an example using Northwind:
DECLARE @vTable TABLE (TID Int, TDesc Varchar(255))
INSERT INTO @vTable
SELECT
TerritoryID,
TerritoryDescription
FROM
Territories
WHERE
TerritoryDescription = 'Denver'
OR TerritoryDescription = 'Edison'
INSERT INTO @vTable
SELECT
TerritoryID,
TerritoryDescription
FROM
Territories
WHERE
TerritoryDescription <> 'Denver'
OR TerritoryDescription <> 'Edison'
ORDER BY TerritoryDescription
SELECT * FROM @vTable
(Sorry about the formatting. I've never been able to get it right  )
Personally I would use Colin's method unless there's no way you can add another column...
The man who smiles when things go wrong has thought of someone he can blame it on.
If you tell a man there are 300 billion stars in the universe, he'll believe you. But if you tell him a bench has just been painted, he'll have to touch it to be sure.
-- modified at 7:52 Monday 10th October, 2005
|
|
|
|
|
Yeah there's a bit more of an overhead with the temp table.
I can put another row in very easily, so will do Colin's route. Was just curious to exactly what you meant.
Thank you very much for your's and Colin's responses though.
Very appreciated.
|
|
|
|
|
You can use union all to achieve this. This is the sybase syntax. I SQL Server 2000 syntax should be the same.
select * from Country where country_name = 'UK' or country_name = 'USA'
union all
select * from Country where country_name <> 'UK' and country_name <> 'USA' order by country_name
|
|
|
|
|
Bingo!!! THANK YOU!!
The final 'order by' on the above in SQL Server, orders the entire rowset. But the above got me thinking, along with Colins original answer of adding a 0, 1 column.
I thought a group by on the individual columns may work, but since my country_id's are all over the place (secondhand data!), that was a no-go. Then thought about adding a 'magic' column to help with the order by.
Finally I've got this, which is exactly what I've been wanting to achieve (n.b. I've got several breakdowns of the UK: United Kingdom (Mainland), United Kingdom (Northern Ireland), etc ):
SELECT country_id, country_name, 0 as seed
FROM country
WHERE country_name LIKE 'United Kingdom%' OR country_name = 'United States'
UNION ALL
SELECT country_id, country_name, 1 as seed from country
WHERE country_name NOT LIKE 'United Kingdom%' AND country_name != 'United States'
ORDER by seed, country_name
I only vaguely remembered that there was a way to do this because I got asked it in an interview earlier in the year, but just couldn't remember how to do it!
Once again THANK YOU!!!!!!!
|
|
|
|
|
There is one more thing. If you want to avoid the seed to be a part of the query you can try the following
select ctm.country_id, ctm.country_name from
(SELECT country_id, country_name, 0 as seed
FROM country
WHERE country_name LIKE 'United Kingdom%' OR country_name = 'United States'
UNION ALL
SELECT country_id, country_name, 1 as seed from country
WHERE country_name NOT LIKE 'United Kingdom%' AND country_name != 'United States'
ORDER by seed, country_name) as ctm
-- modified at 12:32 Monday 10th October, 2005
|
|
|
|
|
Hi all...
I want to find whether a table exists in Access Database or not. I know how to do it in MS SQL Server, we can see the result using Select query from sysobjects table and we have the list of all Tables, SPs, Views, functions...etc. But if my program gets .mdb file i.e. Access Database, my program must find whether a perticular table is created in that database or not.
Is there any way to do it???
Thanx in advance....
|
|
|
|
|
Rohan_bhat_31 wrote: want to find whether a table exists in Access Database or not.
Try this.
SELECT MSysObjects.Name
FROM MSysObjects
WHERE MSysObjects.Name = "tbl_name"
DEBUGGING : Removing the needles from the haystack.
|
|
|
|
|
Hey..thanx buddy....
but where this table is stored??? and there must be some log files also generated behind the scene...like SQL Server...???
looking 4ward 2 see ur reply...
|
|
|
|
|
Rohan_bhat_31 wrote: but where this table is stored??? and there must be some log files also generated behind the scene...like SQL Server...???
Access is completely different than SQL Server. An Access database is basically a binary file, so there is no real "table".
There are no log files. No transaction nothing.
I guess the question becomes what are you attempting to do?
DEBUGGING : Removing the needles from the haystack.
|
|
|
|
|
Hi,
How to get list of all registered databases on mySQL server?
Millan
|
|
|
|
|
hello, I have a question...how can I delete in MySQL the current row...the one where I am currently positioned? is this possible? Thank you.
|
|
|
|
|
hi
handling transaction in asp .net code behhind provides better performance or handling it in SQL Server's Stored procedures in queries like removing list of an entity?
thanks
|
|
|
|
|
Guys, I'm confused so help me out
when trying to connect to a DB (MS Access) that has a password using a connection string like this
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;Jet OLEDB:Database Password=MyDbPassword;"
i used to get that error about a workgroup file missing
so i created one, -which btw i have no idea how it works!- and added it to the connection string
something like this
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;Jet OLEDB:System Database=system.mdw;"
after deploying my application, and installing it, only the user that uses the same PC where i created the workgroup can access the DB, apprently because it joined the workgroup by a simple click
the rest get the following error
System.TypeInitializationException: The type initializer for "CrystalDecisions.CrystalReports.Engine.ReportDocument" threw an exception. ---> System.Runtime.InteropServices.COMException (0x80040154): COM object with CLSID {CF76A644-314B-404D-8D45-F08B51FF990B} is either not valid or not registered.
which btw have nothing to do with the CR nor the merge modules...because after long discussion and search, it all pointing to the added security for the DB
so, guys, why do i need to create a workgroup? why not just a password?
it is a multi user DB ...
If the workgroup is required, then, how can i add users if i'm using domain log-in mode?..i tried using the user IDs they are using to access the domain...still, nothing worked
One more thing, the users' levels are defined within the application, so their access mode on the DB level is the same
|
|
|
|
|
Ranoush wrote: when trying to connect to a DB (MS Access) that has a password using a connection string like this
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;Jet OLEDB:Database Password=MyDbPassword;"
i used to get that error about a workgroup file missing
Change your connection string.
strConnection = _T("Driver={Microsoft Access Driver (*.mdb)};"
"Dbq=c:\\DatabasePath\\dbaccess.mdb;Pwd=MyDbPassword;");
or
strConnection = _T("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\DatabasePath\\dbaccess.mdb; Pwd=MyDbPassword;");
That should get rid of the need for a system db.
DEBUGGING : Removing the needles from the haystack.
|
|
|
|
|
Hello all,
There has got to be an answer for this. I am running Visual Studio 2003 and SQL Server 2000 Client on Windows XP and created a database project. Everything is working great. I can look at tables, and connect. What I can not do is generate scripts, and design. It is like it is not an option. I reinstalled the client tools from SQL. I have tried everything, but generate Create script will not show up. If anyone is familar with this can you please help. Here is my id pitchiah4u@hotmail.com
Pitchiah
Pranav Softsol Pvt Ltd.
|
|
|
|
|
I have a datagird and a checkbox web control within the datagrid, I am trying to step through each datagriditem and detect whether the checkbox is checked or not, however when testing this application, the checkbox always shows unchecked? I have bound the checked property to a column in the datagrid, and added a button shich will execute code to delete all lines whose checkbox is checked. Please Help!
|
|
|
|
|
Hello
I just installed Visual studio 2005 RC1 and after that Sql server 2005 september ctp. The installation of both programs went well and no error was shown.
Unfortunatly there is not any trace of Management Studio. Under Start Menu\Programs\Microsoft SQL Server 2005 CTP i only have Configuration Tools and nothing else.
In the installation of sql server 2005 september ctp i choosed all client tools (i choosed every checkbox for all programs).
Does anyone have any good idea?
|
|
|
|
|
The trick is to install SQL 2005 september ctp before installing Visual Studio 2005 RC1.
|
|
|
|
|
I am new to SQL.
I am facing a problem.
I have two tables as under:
Sales
+-------+-------+---------------+---------------+
|ID |Date |ProductID |Sold_Qty |
+-------+-------+---------------+---------------+
|1 |5/25/05|1 |4 |
|2 |5/25/05|2 |3 |
|3 |5/26/05|1 |2 |
|4 |5/26/05|1 |1 |
|5 |5/26/05|2 |2 |
+-------+-------+---------------+---------------+
SalesReturn
+-------+-------+---------------+---------------+
|ID |Date |ProductID |Returned_Qty |
+-------+-------+---------------+---------------+
|1 |5/26/05|1 |2 |
|2 |5/27/05|2 |1 |
|3 |5/27/05|1 |2 |
+-------+-------+---------------+---------------+
How to create a query that can tell me about each product sales activity (How many sold and how many returned)
And can preview as under
+---------------+---------------+---------------+
|ProductID |T_Sold_Qty |T_Returned_Qty |
+---------------+---------------+---------------+
|1 |7 |4 |
|2 |5 |1 |
+---------------+---------------+---------------+
Note: I have tried "SELECT SUM([Sold_Qty]) As T_Sold_Qty WHERE ProductID=1"
This query works only in one table and for only one ProductID
What about the following Query?
"SELECT Products.ProductID, (SELECT SUM([Sales.Sold_Qty]) As T_Sold_Qty FROM Sales WHERE Sales.ProductID=Products.ProductID), (SELECT SUM([SalesReturn.Returned_Qty]) As T_Returned_Qty FROM SalesReturn WHERE SalesReturn.ProductID=Products.ProductID) FROM Products,Sales, SalesReturn "
Shoaib Nawaz
-- modified at 16:38 Thursday 6th October, 2005
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
