How do I return row numbers with my query?

Often, people want to "invent" an identity, or rank, on the fly. So their original result set would look like this:

Lastname Firstname
-------- ---------
Evans Bob
Smith Frank

And they would want this:

Rownum Lastname Firstname
------ -------- ---------
1 Evans Bob
2 Smith Frank

This would act like Oracle's ROWNUM, which isn't supported in SQL Server.

Of course, once you've retrieved this resultset into your ASP page, you could use a counter to increment as you're processing. This is by the easiest way, e.g.

<%
' ...
set rs = conn.execute(sql)
counter = 0
do while not rs.eof
counter = counter + 1
response.write counter & " "
response.write rs(0) & "
"
rs.movenext
loop
' ...
%>

However, some people really, really, really want the row number to come back from the database. It's a little less efficient, but let's examine a few methods. Given this sample data:

SET NOCOUNT ON

CREATE TABLE people
(
firstName VARCHAR(32),
lastName VARCHAR(32)
)
GO

INSERT people VALUES('Aaron', 'Bertrand')
INSERT people VALUES('Andy', 'Roddick')
INSERT people VALUES('Steve', 'Yzerman')
INSERT people VALUES('Steve', 'Vai')
INSERT people VALUES('Joe', 'Schmoe')

The first method we'll try is a COUNT with a GROUP BY:

SELECT
rank = COUNT(*),
a.firstName,
a.lastName
FROM
people a
INNER JOIN people b
ON
a.lastname > b.lastname
OR
(
a.lastName = b.lastName
AND
a.firstName >= b.firstName
)
GROUP BY
a.firstName,
a.lastName
ORDER BY
rank

We can also try a COUNT as a subquery, which doesn't require GROUP BY (which means you could include other columns in the outer query).

SELECT
rank = (
SELECT COUNT(*)
FROM people b
WHERE
a.lastname > b.lastname
OR
(
a.lastName = b.lastName
AND a.firstName >= b.firstName
)
),
a.firstName,
a.lastName
FROM
people a
ORDER BY
a.firstName,
a.lastName

Results in all cases:

rank firstName lastName
---- --------- --------
1 Aaron Bertrand
2 Andy Roddick
3 Joe Schmoe
4 Steve Vai
5 Steve Yzerman

Note that if you have duplicates in your table, you will end up with something like this:

1 Aaron Bertrand
3 Joe Schmoe
3 Joe Schmoe

So, to avoid this, you might want to make sure that either (a) you avoid and remove duplicates (see Article #2431); or (b) if duplicates are allowed and make sense for your data model, that you have some other primary key or unique identifier. Then, you can make it a part of the query; for example:

SET NOCOUNT ON

CREATE TABLE people
(
peopleID INT IDENTITY(1,1) PRIMARY KEY,
firstName VARCHAR(32),
lastName VARCHAR(32)
)
GO

INSERT people VALUES('Aaron', 'Bertrand')
INSERT people VALUES('Andy', 'Roddick')
INSERT people VALUES('Steve', 'Yzerman')
INSERT people VALUES('Steve', 'Yzerman')
INSERT people VALUES('Steve', 'Vai')
INSERT people VALUES('Joe', 'Schmoe')

SELECT
rank = (
SELECT COUNT(*)
FROM people b
WHERE a.lastName > b.lastName
OR
(
a.lastname = b.lastname
AND a.firstName >= b.firstName
)
) - (
SELECT COUNT(*) FROM
people b
WHERE a.lastName = b.lastName
AND a.firstName = b.firstName
AND a.peopleID < b.peopleID
),
a.firstName,
a.lastName
FROM
people a
ORDER BY
a.lastName,
a.firstName

Results:

rank firstName lastName
---- --------- --------
1 Aaron Bertrand
2 Andy Roddick
3 Joe Schmoe
4 Steve Vai
5 Steve Yzerman
6 Steve Yzerman

Grouping within groups

Often, you'll want a more complex row number scheme, for example you might want to rank within groups of a hierarchy. Let's say we wanted to list sports teams, and assign "ranks" alphabetically, within each city:

CREATE TABLE #teams
(
city VARCHAR(20),
team VARCHAR(20)
)

SET NOCOUNT ON

INSERT #teams SELECT 'Boston', 'Celtics'
INSERT #teams SELECT 'Boston', 'Bruins'
INSERT #teams SELECT 'Boston', 'Red Sox'
INSERT #teams SELECT 'New York', 'Yankees'
INSERT #teams SELECT 'New York', 'Mets'
INSERT #teams SELECT 'New York', 'Knicks'
INSERT #teams SELECT 'New York', 'Rangers'
INSERT #teams SELECT 'New York', 'Islanders'
INSERT #teams SELECT 'New York', 'Jets'
INSERT #teams SELECT 'New York', 'Giants'
INSERT #teams SELECT 'Chicago', 'Black Hawks'
INSERT #teams SELECT 'Chicago', 'Cubs'
INSERT #teams SELECT 'Chicago', 'White Sox'
INSERT #teams SELECT 'Chicago', 'Bears'
INSERT #teams SELECT 'New England', 'Patriots'

SELECT city, team, rank =
(
SELECT COUNT(*)
FROM #teams t2
WHERE t2.city = t1.city
AND t2.team <= t1.team
)
FROM #teams t1
ORDER BY city, team

DROP TABLE #teams

Results:

city team rank
------------ ------------ ----
Boston Bruins 1
Boston Celtics 2
Boston Red Sox 3
Chicago Bears 1
Chicago Black Hawks 2
Chicago Cubs 3
Chicago White Sox 4
New England Patriots 1
New York Giants 1
New York Islanders 2
New York Jets 3
New York Knicks 4
New York Mets 5
New York Rangers 6
New York Yankees 7


Keep in mind that, since your presentation tool (Crystal Reports, ASP, PHP, what have you) is going to have to treat every row separately anyway, it makes sense to just retrieve the rows in the correct order, and let the application compare every row to see if this is a new city or not, and accordingly increment the count or start over. This will greatly reduce the amount of strain you're putting on the database.

Just curious.

You know that flashing/shaking banner ad at the top of the page that says, basically "If this ad is flashing/shaking, congratulations, you are a winner. Click on this ad to get your free prize". Does it ever say "sorry bud, this ad isn't shaking or flashing so you're a loser. Dont' bother clicking on this ad cause we'll charge you for everything (and sell your email address too)"?

Too funny to miss...sadly kinnda true

Profane Computers

Could this be Nolan Curtis' long lost brother?

From: George Wiman
Sent: Monday, April 21, 2003 1:12 PM
To: submissions@bbspot.com
Subject: Profanity and computers


I know the popular theory is that high-tech devices run on electricity. But that theory is wrong: high-tech devices run on profanity. When was the last time you swore at a lamp? Lamps run on electricity, not profanity. Computers have several small lamps in them, which is why they need some electricity, but swearing at a computer really does make it run better.
- -
Having been in the computer support field for eight years, I feel qualified to make this statement. People think I'm a technical guru but my secret is constantly muttering profanity under my breath while I fix their systems. It's easy - anyone can learn to do it.
- -
As an example, this weekend, I needed to run a network cable from the router in my basement to my workbench. My terminal crimper was broken, so I set out to buy a new one. Swearing dutifully at traffic on the way, I arrived safely to purchase the replacement crimper and some CAT5 certified RJ45 cable ends.
- -
CAT5e cable is expensive, but I didn't have to buy any thanks to the wastefulness of several construction projects around town (an ample supply can be found in dumpsters.) I selected a long piece from my box of salvaged cable and arranged the twisted wires for crimping. Since I am quite dyslexic, I got the order wrong, resulting in a nonfunctional cable and nearly an hour's troubleshooting with network properties on the computer. What happened?
- -
Well, I remembered to swear at the computer, so the network properties were set correctly. Wait! I forgot to cuss out the cable! Sure enough, I had calmly and confidently set the wires into the terminals and crimped them, without comparing them to excrement or questioning if the wire was excessively attached to its mother. The cable could not work.
- -
Correcting that error, I cut off the incorrectly made terminals. I again consulted the cable chart, cursing the design of the chart that had mislead my dyslexic vision, the maker of the crimping tool, and the manufacturer of the wire for making the color-coding too hard to see. (Never mind that my eyesight isn't what it used to be.) Need I add that the cable now worked perfectly?
- -
A technician friend of mine says that high-tech devices run on smoke, not profanity. He says that everything works fine until a malfunction lets the smoke out, and after that the device doesn't work anymore. He could be right, though I notice he liberally applies an astounding range of obscenity while fixing systems. He is a master technician, covering all the bases.
- -
Mark Twain said that he "...found in profanity solace unexcelled by prayer." People who object to 'bad' words as an affront to morality need to stop and give thanks for everything the high-tech revolution has given them. All over the world, tech people are swearing, cussing, inventing new obscene suggestions for Bill Gates, just to keep the world's technology working. It's not just a job, it's a calling.

George Wiman, Computer Support Specialist

How do I cast a string to an int, float, double, etc?

How do I cast a string to an int, float, double, etc?

You can use the Convert class or the Parse method of the built-in type you are casting to. i.e.

string myStr = "123";
int myParsedInt = Int32.Parse(myStr);
int myConvertedInt = Convert.ToInt32(myStr);

This example uses the int type, but you can also use the same techniques for any of the other integral or floating point types.

OPen Source...it aint as bad

I was at a conference when two techies walked into the open bar, one an open source supporter and one staunchly anti. They got into it a bit after a few drinks, and Mr. Anti commented loudly, “I wish open source would just go away! It causes more trouble than it's worth.” Statements I obviously have issues with. Now, I know most people don’t understand the role of open source software in our world, or just how many services that we take for granted would disappear without it. If you’re a card-carrying member of the community, you probably know where I’m headed.

Say at the stroke of midnight, all open source software magically vanished. What would still work tomorrow?

For starters, the Internet would “disappear” for the average user. Most Domain Name Servers (DNS) are run on open source software like BIND, which turns www.whurley.com into the IP address of the appropriate server. The majority of basic Internet users would be literally lost in translation. Of course, BIND isn't the only open source software for DNS. And not all DNS solutions are open source.

So assume DNS still works or perhaps you memorized 72.14.207.99 instead of www.google.com. Even with name servers functioning, Google would drop off of the face of the Internet. Google is primarily powered by Linux—arguably the most popular open source operating system on the planet. No worries. You'll just pop over to Yahoo!, right? Wrong. Yahoo! is one of the largest consumers of another popular open source operating system: FreeBSD. Now you’ve resigned yourself to trying 207.68.172.246. We all know they're not running open source, and they've been working hard on that search feature for quite some time.

Ok, MSN is up and running, now execute a search. I heard a sweet Shakira remix on the radio this morning; I’m going to search for that. MSN returns a list of sites offering the song . . . I’m clicking on them . . . and . . . nothing. No dancing? No Latin rhythms? Over 60% of all Internet sites are powered by Apache, an open source web server. Before I even click on a link, my chances of success have been reduced to 4 in 10.

Of the 118,023,363 sites surveyed by NetCraft so far in the month of May, just over 70 million of them wouldn't work if open source software were to disappear. Of course, Apache isn't the only open source web server and . . . you know the rest. I could go on and on about how none of your online transactions would be secure without OpenSSH and OpenSSl and all the other services users access every day that wouldn’t exist in this scenario.

Open source is not a new trend. It’s not a fad. It’s everywhere, whether you recognize it or not. From the embedded Linux in new wireless routers to Firefox, the world's most popular open source browser, open source powers the Internet and countless other technologies.

You already know I’m a true believer, but what do you all think? I’d like to hear your thoughts on how the disappearance of open source would affect you.

Installing Apache PHP and MySQL

PHP and MySQL are usually associated with LAMP (Linux, Apache, MySQL, PHP).
However, most PHP developer ( including me ) are actually using Windows when
developing the PHP application. So this page will only cover the WAMP ( Windows,
Apache, MySQL, PHP ). You will learn how to install Apache, PHP, and MySQL
under Windows platform.

The first step is to download the packages :

• Apache : www.apache.org


• PHP : www.php.net



• MySQL : www.mysql.com

You should get the latest version of each packages. As for the example in
this tutorial i'm using Apache 2.0.50 ( apache_2.0.50-win32-x86-no_ssl.msi
), PHP 4.3.10 ( php-4.3.10-Win32.zip ) and MySQL 4.0.18 ( mysql-4.0.18-win.zip
).

Now let's start the installation process one by one.

• Installing Apache



• Installing PHP


• Modifying Apache Configuration


• Installing MySQL


• Modifying PHP Configuration File

Installing Apache Installing apache is easy if you download the Microsoft Installer ( .msi ) package. Just double click on the icon to run the installation wizard. Click next until you see the Server Information window. You can enter localhost for both the Network Domain and Server Name. As for the administrator's email address you can enter anything you want. I'm using Windows XP and installed Apache as Service so everytime I start Windows Apache is automatically started. Click the Next button and choose Typical installation. Click Next one more time and choose where you want to install Apache ( I installed it in the default location C:\Program Files\Apache Group ). Click the Next button and then the Install button to complete the installation process. To see if you Apache installation was successful open up you browser and type http://localhost in the address bar. You should see something like this :   By default Apache's document root is set to htdocs directory. The document root is where you must put all your PHP or HTML files so it will be process by Apache ( and can be seen through a web browser ). Of course you can change it to point to any directory you want. The configuration file for Apache is stored in C:\Program Files\Apache Group\Apache2\conf\httpd.conf ( assuming you installed Apache in C:\Program Files\Apache Group ) . It's just a plain text file so you can use Notepad to edit it. For example, if you want to put all your PHP or HTML files in C:\www just find this line in the httpd.conf : DocumentRoot "C:/Program Files/Apache Group/Apache2/htdocs" and change it to : DocumentRoot "C:/www" After making changes to the configuration file you have to restart Apache ( Start > Programs > Apache HTTP Server 2.0.50 > Control Apache Server > Restart ) to see the effect. Another configuration you may want to change is the directory index. This is the file that Apache will show when you request a directory. As an example if you type http://www.php-mysql-tutorial.com/ without specifying any file the index.php file will be automatically shown. Suppose you want apache to use index.html, index.php or main.php as the directory index you can modify the DirectoryIndex value like this : DirectoryIndex index.html index.php main.php Now whenever you request a directory such as http://localhost/ Apache will try to find the index.html file or if it's not found Apache will use index.php. In case index.php is also not found then main.php will be used.

Installing PHP First, extract the PHP package ( php-4.3.10-Win32.zip ). I extracted the package in the directory where Apache was installed ( C:\Program Files\Apache Group\Apache2 ). Change the new created directory name to php ( just to make it shorter ). Then copy the file php.ini-dist in PHP directory to you windows directory ( C:\Windows or C:\Winnt depends on where you installed Windows ) and rename the file to php.ini. This is the PHP configuration file and we'll take a look what's in it later on. Next, move the php4ts.dll file from the newly created php directory into the sapi subdirectory. Quoting from php installation file you can also place php4ts.dll in other places such as : In the directory where apache.exe is start from ( C:\Program Files\Apache Group\Apache2 \bin) In your %SYSTEMROOT%\System32, %SYSTEMROOT%\system and %SYSTEMROOT% directory. Note: %SYSTEMROOT%\System32 only applies to Windows NT/2000/XP) In your whole %PATH% Side Note : Thanks to Shannon Tang for pointing this out

Modifying Apache Configuration Apache doesn't know that you just install PHP. We need to tell Apache about PHP and where to find it. Open the Apache configuration file in C:\Program Files\Apache Group\Apache2\conf\httpd.conf and add the following three lines : LoadModule php4_module php/sapi/php4apache2.dll AddType application/x-httpd-php .php AddType application/x-httpd-php-source .phps The first line tells Apache where to load the dll required to execute PHP and the second line means that every file that ends with .php should be processed as a PHP file. You can actually change it to anything you want like .html or even .asp! The third line is added so that you can view your php file source code in the browser window. You will see what this mean when you browse this tutorial and click the link to the example's source code like this one. Now restart Apache for the changes to take effect ( Start > Programs > Apache HTTP Server 2.0.50 > Control Apache Server > Restart ) . To check if everything is okay create a new file, name it as test.php and put it in document root directory ( C:\Program Files\Apache Group\Apache2\htdocs ). The content of this file is shown below. <?php phpinfo(); ?> phpinfo() is the infamous PHP function which will spit out all kinds of stuff about PHP and your server configuration. Type http://localhost/test.php on your browser's address bar and if everything works well you should see something like this :

Installing MySQL First extract the package ( mysql-4.0.18-win.zip ) to a temporary directory, then run setup.exe. Keep clicking the next button to complete the installation. By default MySQL will be installed in C:\mysql. Open a DOS window and go to C:\mysql\bin and then run mysqld-nt --console , you should see some messages like these : C:\mysql\bin>mysqld-nt --console InnoDB: The first specified data file .\ibdata1 did not exist: InnoDB: a new database to be created! 040807 10:54:09 InnoDB: Setting file .\ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 040807 10:54:11 InnoDB: Log file .\ib_logfile0 did not exist: new to be created InnoDB: Setting log file .\ib_logfile0 size to 5 MB InnoDB: Database physically writes the file full: wait... 040807 10:54:12 InnoDB: Log file .\ib_logfile1 did not exist: new to be created InnoDB: Setting log file .\ib_logfile1 size to 5 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 040807 10:54:31 InnoDB: Started mysqld-nt: ready for connections. Version: '4.0.18-nt' socket: '' port: 3306 Now open another DOS window and type C:\mysql\bin\mysql if your installation is successful you will see the MySQL client running : C:\mysql\bin>mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.0.18-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> Type exit on the mysql> prompt to quit the MySQL client. Now let's install MySQL as a Service. The process is simple just type mysqld-nt --install to install the service and net start mysql to run the service. But make sure to shutdown the server first using mysqladmin -u root shutdown C:\mysql\bin>mysqladmin -u root shutdown C:\mysql\bin>mysqld-nt --install Service successfully installed. C:\mysql\bin>net start mysql The MySQL service was started successfully. C:\mysql\bin>mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.0.18-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>

Modifying PHP Configuration File ( php.ini ) PHP stores all kinds of configuration in a file called php.ini.You can find this file in the directory where you installed PHP. Sometimes you will need to modify this file for example to use a PHP extension. I won't explain each and every configuration available just the ones that often need modification or special attention. Some of the configurations are : register_globals error_reporting and display_errors extension and extension_path session.save_path max_execution_time register_globals Before PHP 4.2.0 the default value for this configuration is On and after 4.2.0 the default value is Off. The reason for this change is because it is so easy to write insecure code with this value on. So make sure that this value is Off in php.ini. error_reporting and display_errors Set the value to error_reporting = E_ALL during development but after production set the value to error_reporting = E_NONE . The reason to use E_ALL during development is so you can catch most of the nasty bugs in your code. PHP will complain just about any errors you make and spit out all kinds of warning ( for example if you're trying to use an uninitialized variable ). However, after production you should change the value to E_NONE so PHP will keep quiet even if there's an error in your code. This way the user won't have to see all kinds of PHP error message when running the script. One important thing to note is that you will also need to set the value of display_erros to On. Even if you set error_reporting = E_ALL you will not get any error message ( no matter how buggy our script is ) unless display_errors is set to On. extension and extension_path PHP4 comes with about 51 extensions such as GD library ( for graphics creation and manipulation ), CURL, PostgreSQL support etc. These extensions are not turned on automatically. If you need to use the extension, first you need to specify the location of the extensions and then uncomment the extension you want. The value of extension_path must be set to the directory where the extension is installed which is PHP_INSTALL_DIR/extensions, with PHP_INSTALL_DIR is the directory where you install PHP. For example I installed PHP in C:\Program Files\Apache Group\Apache2\php so the extensions path is : extension_path = C:/Program Files/Apache Group/Apache2/php/extensions/ Don't forget to add that last slash or it won't work After specifying the extension_path you will need to uncomment the extension you want to use. In php.ini a comment is started using a semicolon (;). As an example if you want to use GD library then you must remove the semicolon at the beginning of ;extension=php_gd2.dll to extension=php_gd2.dll session.save_path This configuration tells PHP where to save the session data. You will need to set this value to an existing directory or you will not be able to use session. In Windows you can set this value as session.save_path = c:/windows/temp/ max_execution_time The default value for max_execution_time is 30 ( seconds ). But for some scripts 30 seconds is just not enough to complete it's task. For example a database backup script may need more time to save a huge database. If you think your script will need extra time to finish the job you can set this to a higher value. For example to set the maximun script execution time to 15 minutes ( 900 seconds ) you can modify the configuration as max_execution_time = 900 PHP have a convenient function to modify PHP configuration in runtime, ini_set(). Setting PHP configuration using this function will not make the effect permanent. It last only until the script ends.

Converting C# month from int to Name

If you want to know what month no. 1 is in text, this piece of code comes in handy

DateTime.Parse("01-JAN-1970").AddMonths(intMonth ).ToString("MMMM")

Handy tip

This assumes that intMonth is zero-based. Otherwise, use intMonth - 1. If you're doing it repeatedly, you want to store the results of DateTime.Parse(), natch.

Selecting concatanation fo two columns in SQL

Okie...if you want to select firstname + lastname as fullName its eazy ...and anyone can do it...

But, what when you want to select three numbers concatanated...namely, month, date and year...

try using "+" and the SQL server gives you the sum of the three numbers which is no good...

Here is a tiny trick i played on SQL server...

SELECT (Upper(MONTH)) + '/'+Upper(Day)+'/' +Upper(Year) as Date FROM DateTable...

when the SQL server does toUpper function, it assumes, the argument is a string and volah..the query runs seemlessly

Have fun coding...

Dropdown calendar for C#

front end


<h2> IAI Calendar</h2> <br /> <div style="width:100%;">
<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged">
</asp:DropDownList> <asp:DropDownList ID="DropDownList2" runat="server"
AutoPostBack="True" OnSelectedIndexChanged="DropDownList2_SelectedIndexChanged">
</asp:DropDownList> <br /> <asp:Label ID="Label1" runat="server"></asp:Label><br
/> </div>






backend



protected void Page_Load(object sender, EventArgs e) { DateTime tnow = DateTime.Now;
ArrayList AlYear = new ArrayList(); int i; for (i = 2002; i <= 2010; i++) AlYear.Add(i);
DateTime month = Convert.ToDateTime("1/1/2000"); for(i=0;i<12;i++) { DateTime
NextMont = month.AddMonths(i); ListItem list = new ListItem(); list.Text = NextMont.ToString("MMMM");
list.Value = NextMont.Month.ToString(); DropDownList2.Items.Add(list); } if (!IsPostBack)
{ DropDownList1.DataSource = AlYear; DropDownList1.DataBind(); DropDownList1.SelectedValue
= tnow.Year.ToString(); DropDownList2.SelectedValue = tnow.Month.ToString(); } Label1.Text
= "You select date:" + DropDownList1.SelectedValue + "year" + DropDownList2.SelectedValue
+ "month" ; } public void DropDownList1_SelectedIndexChanged(object sender, EventArgs
e) { } public void DropDownList2_SelectedIndexChanged(object sender, EventArgs e)
{ }