?
Solved

Select Table and count  IDENTITY

Posted on 2004-10-21
7
Medium Priority
?
226 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
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
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!

 
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

616 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