Solved

Select Table and count  IDENTITY

Posted on 2004-10-21
209 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
Question by:ezroy
    7 Comments
     
    LVL 7

    Expert Comment

    by:SQL_Stu
    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
    oops, no, that won't work!!  I misread your question...
    0
     
    LVL 14

    Expert Comment

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

    itsvtk
    0
     
    LVL 11

    Expert Comment

    by:rdrunner
    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
    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:
    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
    i dont have ' Key ' filed,
    i just wnat to count   result ,
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Suggested Solutions

    SQL Server Side Trace is a technique of Profiling SQL Server Events Silently (i.e without Using the Profiling Tool). Running a visual tool in production increases overhead, but we can develop server side Trace using Sql Server Profiler itself. We…
    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    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
    Viewers will learn how the fundamental information of how to create a table.

    934 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now