[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Select Table and count  IDENTITY

Posted on 2004-10-21
7
Medium Priority
?
223 Views
Last Modified: 2012-06-21
select   name, tel from User  where sex='f'


name, tel
-----------
Vivia  008-855
Jan    854-526

how do i select  data  like this ?

key , name, tel
--------------
1 Vivia  008-855
2 Jan    854-526
3  





0
Comment
Question by:ezroy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 7

Expert Comment

by:SQL_Stu
ID: 12368354
You could try:

Select count(*) as key, name, tel
from User
where sex = 'F'
group by name, tel
order by count(*)

0
 
LVL 7

Expert Comment

by:SQL_Stu
ID: 12368361
oops, no, that won't work!!  I misread your question...
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 12368425
select name, tel, ( select count(*) from user where  name <= t1.name )
from User  t1
where sex='f'
order by name

itsvtk
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 11

Expert Comment

by:rdrunner
ID: 12368438
Well actually the "key" should be the "same" for both male and female entries.

If your table looks like this:

key , name, tel
--------------
1 Vivia  008-855
2 Sue    854-526
3 Jan    854-526
4 Mary 555-555

then your query should return  

1 Vivia  008-855
3 Jan    854-526

The key should define the row in the database and not the row in the returned result. If you take the key of the result for example, then there could be another entry added between Jan and Vivian and the IDs in the result changed. If you return the true IDs in the DB you will still be able to modify "Jan" with the ID of 3 since the new guy gets a new ID (5)

Hope this makes sense...
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 12368444
or this..

select name, tel, ( select count(*) from user where  tel <= t1.tel )
from User  t1
where sex='f'
order by tel

itsvtk


0
 
LVL 14

Accepted Solution

by:
Thandava Vallepalli earned 500 total points
ID: 12368469
sorry... i forgot to consider sex condition.... in subquery

select name, tel, ( select count(*) from user where  tel <= t1.tel and sex = 'f' )
from User  t1
where sex='f'
order by tel


itsvtk
0
 

Author Comment

by:ezroy
ID: 12368684
i dont have ' Key ' filed,
i just wnat to count   result ,
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question