dcsimg
Results 1 to 10 of 10

Thread: Unique records from a sql server table

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Aug 2016
    Posts
    31

    Unique records from a sql server table

    i have this following table i need query unique records for a specific PtnID(field) from the table
    select * from table where ptnid=201300025 and i should select unique records of PresNo(field)

    as shown in picture
    Name:  Inptntrtmed.jpg
Views: 163
Size:  13.6 KB
    Attached Images Attached Images  
    Last edited by yousufkhan73; Aug 8th, 2018 at 08:34 AM.

  2. #2
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,301

    Re: Unique records from a sql server table

    Code:
    select  PtnID,PresNo from table where ptnid=201300025
    group by PtnID,PresNo
    Slow as hell.

  3. #3
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,001

    Re: Unique records from a sql server table

    If you want only the PresNo field for a specific ptnid you can do this:
    Code:
    SELECT PresNo
    FROM table 
    WHERE ptnid=201300025 
    GROUP BY PresNo
    If you also want ptnid, just add it to the Select list and the Group By list. (then you can remove the Where clause, so you see each PresNo value for each ptnid)


    If you also want other fields to be displayed, you somehow need to explicitly tell SQL Server what you want displayed for those fields.

  4. #4

    Thread Starter
    Junior Member
    Join Date
    Aug 2016
    Posts
    31

    Re: Unique records from a sql server table

    Quote Originally Posted by si_the_geek View Post
    If you want only the PresNo field for a specific ptnid you can do this:
    Code:
    SELECT PresNo
    FROM table 
    WHERE ptnid=201300025 
    GROUP BY PresNo
    If you also want ptnid, just add it to the Select list and the Group By list. (then you can remove the Where clause, so you see each PresNo value for each ptnid)


    If you also want other fields to be displayed, you somehow need to explicitly tell SQL Server what you want displayed for those fields.
    i need all fields that's y i asked the question, grouping is simple but it is not solving my issue just have a look at picture i posted
    i want to have all fields

  5. #5
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,848

    Re: Unique records from a sql server table

    The picture is indiscernible. Unfortunately images don't post well on this forum. If you rely on them you're unlikely to get much help I'm afraid

    The best way to post questions like this is to describe exactly what you want and post some example data and the expected result. Actually, the best way would be to post a sql script that creates a temp table and fills it with some example data. Give us that alongside an expected result and you're likely to get plenty of help.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  6. #6
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,001

    Re: Unique records from a sql server table

    Quote Originally Posted by yousufkhan73 View Post
    i need all fields that's y i asked the question, grouping is simple but it is not solving my issue just have a look at picture i posted
    i want to have all fields
    In that case:
    Quote Originally Posted by yousufkhan73 View Post
    Quote Originally Posted by si_the_geek View Post
    ...
    If you also want other fields to be displayed, you somehow need to explicitly tell SQL Server what you want displayed for those fields.
    ...
    SQL Server is not willing to attempt to guess what you want, you need to tell it.

    As you haven't given any hints which value(s) you want displayed, we can't tell you how to make that happen.

    What you might want is something like:
    ItemCode: the lowest value (use Min(ItemCode) )
    TotQuantity: the total (use Sum(TotQuantity) )
    ...but you need to decide for each field what you want.

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Aug 2016
    Posts
    31

    Re: Unique records from a sql server table

    I have already mentioned in first question i need unique record for a specific PtnID it is not like that for sql server to guess i thought you will understand by seeing the result its ok
    i will try to explain it again in following way

    I have table called InPtnMedicine
    Fields
    PtnID int
    InptnID int
    PresNo int
    PresSNo int
    PresDate date
    One PtnID can have multiple PresNo and one PresNo can have multiple PresSNo
    I want Ptnid, InptnID, PresNo, PresDate I don’t want PresSno this duplicate records
    I tried this query

    Select PtnID, InptnID,PresNo, PresDate from InPtnMedcine where PtnID=xxxxx and Pressno in (select distinct Presno from InPtnMedicine where PtnID=xxxxx)
    But still I don’t get the unique records

  8. #8

    Thread Starter
    Junior Member
    Join Date
    Aug 2016
    Posts
    31

    Re: Unique records from a sql server table

    I have already mentioned in first question i need unique record for a specific PtnID it is not like that for sql server to guess i thought you will understand by seeing the result its ok
    i will try to explain it again in following way

    I have table called InPtnMedicine
    Fields
    PtnID int
    InptnID int
    PresNo int
    PresSNo int
    PresDate date
    One PtnID can have multiple PresNo and one PresNo can have multiple PresSNo
    I want Ptnid, InptnID, PresNo, PresDate I don’t want PresSno this duplicate records
    I tried this query

    Select PtnID, InptnID,PresNo, PresDate from InPtnMedcine where PtnID=xxxxx and Pressno in (select distinct Presno from InPtnMedicine where PtnID=xxxxx)
    But still I don’t get the unique records

  9. #9
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,848

    Re: Unique records from a sql server table

    Sorry but you're still not being very clear. Is this what you're after:-
    Code:
    Select Distinct PtnID, InptnID,PresNo, PresDate
    From InPtnMedcine
    where PtnID=xxxxx
    
    --Or for those who dislike Distincts
    Select PtnID, InptnID,PresNo, PresDate
    From InPtnMedcine
    where PtnID=xxxxx
    Group By PtnID, InptnID,PresNo, PresDate
    If that's not what you're after I'd refer you to my previous suggestion: Show us sample data and the expected result.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  10. #10
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,848

    Re: Unique records from a sql server table

    By the way, part of the confusion may be coming from the fact that your data structure isn't ideal. If you're in a position to change that then the following might prove useful. Otherwise ignore it (sometimes we just have to work with what we've got)

    You said "One PtnID can have multiple PresNo and one PresNo can have multiple PresSNo ". That means PresNo should really be in it's own table with a foreign key to InPtnMedcine . And PresSNo should be in it's own table with a foreign key to the PresNo table.

    If you had this structure you would not need to do any aggregation to eliminate the duplicates. You would simply join InPtnMedcine to the PresNo table and NOT join to the PresSNo table.

    Any time you find yourself doing an aggregation purely to eliminate duplicates it's a strong smell that something should be broken out into a child table.
    Last edited by FunkyDexter; Aug 9th, 2018 at 11:33 AM.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width