SQL Puzzle

[wileyjack]

Can anyone solve this puzzle with SQL only - no temp tables and no cursors
(sent to me by a friend)?

I would have done it with a cursor on SQL Server but the rules stipulate not using a cursor. Can this be done with just SQL?

You have a table:

First Name Last Name
John Doe
Jeff Smith
etc..

Is it possible to write a query in SQL which assigns serial numbers 1; 2; 3; to the rows(in any sequence)? So the output should look like this:

Number First Name Last Name
1 John Doe
2 Jeff Smith
etc..

Note that your query must work with any data for the input table. You cannot rely on the fact that there are only two rows. Number cannot be a IDENTITY column.

Thanks!

[Chigrik]

It's not possible with ANSI SQL and Transact-SQL cannot make it too.

With best regards,
Alexander Chigrik
http://www.MSSQLCity.com/ - all about MS SQL
(SQL Server Articles, FAQ, Scripts, Tips and Test Exams).

[skhanal]

You can't do it with ANSI SQL.

One trick may be to create a permanent table to hold the last number and create a wrapper function to increment the counter value in that table and use this function in SELECT statement.

[mist]

Why impose these artificial limits in any case? You could do the whole thing by simply altering the table and adding an IDENTITY row. Otherwise you're somewhat forced into using a temporary table.

[wileyjack]

It's just a 'puzzle'. In the real world if I had to do this I would have altered the table myself with the column specs of INT IDENITY SEED 1 INCREMENT 1.

This exercise is for SQL and if it could be done with SQL. Just a puzzle that had me pulling my hair out because I couldn't figure out how to do it with just a SQL statement.

The solutions I came up with were to use IDENTITY, a CURSOR, or a temporary table, but I couldn't do it within the boundaries of the rules given (SQL ONLY).

I would be most enlightened if someone could; evidently it is beyond my skillset.

[EdMann]

Here's the solution. I hope you're not going to use this information to program PL/SQL on SQL Server.

Use Northwind
GO

SELECT count(b.lastname),
a.LastName,
a.FirstName
FROM employees a,
employees b
WHERE a.LastName >= b.lastname
GROUP BY a.LastName, a.FirstName
ORDER BY 1
GO

[wileyjack]

I guess that throws the "can't be done" theory out the window!!!

Thanks! I have been enlightened!!!!!!

[EdMann]

"Set Theory" is the key. I avoid using cursors like the plague.

[skhanal]

Well, the solution looks good because Employee table has unique last names. If you add another row there with same last name as any other row, you will see duplicate number for that row.

Is this what you wanted?

To make it more interesting try running this query after adding a row with same last name first name as any existing one.

[EdMann]

True, but that was just a sample. To put it into pratice, you need to include the PK or AK in your result set.