Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query

Author

Topic

sunitagoswami
Starting Member

10 Posts

Posted - 2006-05-25 : 06:47:25

Can you please write a query as
1. The table goes as
EmpID Emp Dept
123 Admin
789 Systems
989 IT
980 Accounts

I want a Query which will resturn the EmpID in Ascending order while the
Emp Dept in Descending order along with the RowNumber
The Result will be

RowNumber EmpID EmpDept
1 123 Systems
2 789 IT
3 980 Admin
4 989 Accounts

Thanks,
Sunita

Sunita

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-25 : 07:15:32

Select EmpID, EmpDept from yourTable Order by EmpID ASC, EmpDept DESC

Return the result set to the front end application and number them there

Madhivanan

Failing to plan is Planning to fail

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-25 : 07:43:35

ELECT (SELECT COUNT(i.EmpID)
FROM Employee i
WHERE i.EmpID >= o.EmpID) AS RowNumber,
EmpID ,EmpDept
FROM Employee o
ORDER BY RowNumber

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

sunitagoswami
Starting Member

10 Posts

Posted - 2006-05-25 : 08:10:24

Thanks,
Even I got a solution but currently SQL SERVER is not installed please try this

select Identity(int,1,1) as Rownumber, EmpID, EMPDept from table into table order by EmpID ASC, EmpDept DESC

Sunita

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-25 : 09:33:29

chiragkhabaria, yours is not advisible for the table that has millions of rows
sunitagoswami, yours is also not advisible for the same reason also that should be

select Identity(int,1,1) as Rownumber, EmpID, EMPDept into #temp from table order by EmpID ASC, EmpDept DESC

Madhivanan

Failing to plan is Planning to fail

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-05-25 : 10:03:59

And no one is actually giving the required result, but this one will (in 2005):

SELECT D1.I, EmpID, EmpDept
FROM (SELECT ROW_NUMBER() OVER (ORDER BY EmpID ASC) AS I, EmpID FROM Emp) AS D1
INNER JOIN (SELECT ROW_NUMBER() OVER (ORDER BY EmpDept DESC) AS I, EmpDept FROM Emp) AS D2
ON D2.I = D1.I

as I read the requirement was to sort the two coloumns independant of each other.

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-25 : 10:07:37

>>And no one is actually giving the required result, but this one will (in 2005):

The question didnt specify that she is using SQL Server 2005

Madhivanan

Failing to plan is Planning to fail

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-05-25 : 10:15:32

Hehehe true, but none of the others will, no matter which version of SQL Server is used

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-05-25 : 10:28:03

but to make 'madhivanan' happy:

SELECT D1.I, EmpID, EmpDept
FROM (SELECT (SELECT COUNT(EmpID) FROM Emp i WHERE i.EmpID <= o.EmpID) AS I, EmpID FROM Emp o) AS D1
INNER JOIN (SELECT (SELECT COUNT(EmpDept) FROM Emp i WHERE i.EmpDept >= o.EmpDept) AS I, EmpDept FROM Emp o) AS D2
ON D2.I = D1.I
ORDER BY D1.I

works both in 2000 and 2005.

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-25 : 10:35:19

Well. But numbering should be done in front end applications if possible

Madhivanan

Failing to plan is Planning to fail

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-05-25 : 10:47:40

Unless the numbering is part of the solution, and in this case it is ....

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

sunitagoswami
Starting Member

10 Posts

Posted - 2006-05-26 : 01:27:02

I got SQL SERVER 2000 installed today..
Thanks a TON..PSamsig..Its great..

Sunita

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-26 : 03:42:30

quote:
Originally posted by sunitagoswami

I got SQL SERVER 2000 installed today..
Thanks a TON..PSamsig..Its great..

Sunita

Where do you want to show the numbered data?

Madhivanan

Failing to plan is Planning to fail

sunitagoswami
Starting Member

10 Posts

Posted - 2006-05-26 : 08:27:26

Madhivanan,
Just out of curiosity .....

Sunita

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-26 : 11:46:40

quote:
Originally posted by sunitagoswami

Madhivanan,
Just out of curiosity .....

Sunita

No problem. If you use front end application to show the data, use it to number them

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources