A simple question (SELECT)
-
Hi,
CREATE Table UserInfo ( UserName nvarchar(50),Age INT)
GO
INSERT INTO UserInfo (UserName,Age) VALUES('Tom',5)
INSERT INTO UserInfo (UserName,Age) VALUES('Huck',5)
INSERT INTO UserInfo (UserName,Age) VALUES('Mary',5)
GO
I need to make a SELECT query to get the following output:
NoUserName Age
------------------------
1Tom 5
2Huck 5
3Mary 5
What is the SQL Query to get the above output. There is no column 'No' in table.
Thanks in advance.
Unnikrishan
-
A lot of things in life are easier when all your tables have a PRIMARY KEY.
Anyway, see if this helps: http://support.microsoft.com/default.aspx?scid=kb;en-us;186133
But I think you will have a hard time getting exactly this sorting based on your sample data, as this is neither ascending nor descending, but rather seems only to be the order in which the data was entered. So, methods like
select No=count(*), a1.UserName, a1.Age
from UserInfo a1, UserInfo a2
where a1.UserName <= a2.UserName
group by a1.UserName, a1.Age
order by 1
No UserName Age
----------- -------------------------------------------------- -----------
1 Tom 5
2 Mary 5
3 Huck 5
(3 row(s) affected)
or
select No=count(*), a1.UserName, a1.Age
from UserInfo a1, UserInfo a2
where a1.UserName >= a2.UserName
group by a1.UserName, a1.Age
order by 1
No UserName Age
----------- -------------------------------------------------- -----------
1 Huck 5
2 Mary 5
3 Tom 5
(3 row(s) affected)
won't give this resultset.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thank you, Frank.
Regards
Unnikrishnan
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply