dcsimg
Results 1 to 16 of 16

Thread: Unique records from a sql server table

  1. #1

    Thread Starter
    Member
    Join Date
    Aug 2016
    Posts
    37

    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: 469
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,383

    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,290

    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
    Member
    Join Date
    Aug 2016
    Posts
    37

    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,999

    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,290

    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
    Member
    Join Date
    Aug 2016
    Posts
    37

    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
    Member
    Join Date
    Aug 2016
    Posts
    37

    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,999

    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,999

    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

  11. #11

    Thread Starter
    Member
    Join Date
    Aug 2016
    Posts
    37

    Re: Unique records from a sql server table

    Name:  sampledata.jpg
Views: 46
Size:  42.6 KB

    Sample data and expected result

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

    Re: Unique records from a sql server table

    Based on that, this might be right for you:
    Code:
    SELECT Min(InTrtMedID) as InTrtMedID, Min(InPtnID) as InPtnID, PtnID, Min(DocID) as DocID, 
           PresNo, Min(InPtnPres) as InPtnPres, Min(CrtDtTm) as CrtDtTm
    FROM InPtnMedcine 
    WHERE PtnID=201300025 
    GROUP BY PtnID, PresNo
    I can't be sure because most of the sample data you showed already had unique values for the other fields (eg: the CrtDtTm values are always identical for the same PtnID/PresNo), so I can't tell what you want to do for those fields when there are different values... So I highlighted the bits where I guessed.


    This sample data does imply that the suggestion of an extra table in post #10 might be a good idea, but it might just be that you over-simplified the sample data.

  13. #13
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,500

    Re: Unique records from a sql server table

    FD, look at his Sample-Data
    I wouldn't be surprised, if PresNo and PresSNo are in fact Foreign Keys to some other tables.
    Compare the Data in PresNo with the Data in, say, PtnID (or InTrtMedID):
    They follow the same pattern.

    Reading between the Lines:
    PtnID = Patient ID
    InTrtMedID = Internal (?) Treatment/Medication ID
    PresNo = Prescription Number
    etc.
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

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

    Re: Unique records from a sql server table

    I wouldn't be surprised, if PresNo and PresSNo are in fact Foreign Keys to some other tables
    Could be. That would certainly make sense. Although I see a lot of coinciding values eg PresNo and InPtnPresNo which implies this is not properly normalised.

    Sample data and expected result
    Stop posting screenshots/images, they can be extremely difficult to read. Give us sql scripts instead. It really isn't difficult.

    Here are my questions
    1. From what I can make out in your screenshots you want a row for each PresNo, is that correct?
    2. You've selected 2018000001 as the InTrtMedID. Why? Why did you select that over, for example 2018000002?
    3. itnPtnId has the same value in all your sample data. Can we assume that's the only value that will occur for the chosen PtnId? I.e. no PtnId in your database has more than one itnPtnId associated with it?
    4. DocId looks like it's a child of PresNo. So the DocID for PresNo 2018000001 will ALWAYS be 1. For PresNo 2018000001 It will ALWAYS be 4. There is no PresNo in your system that has more than one DocId associated with it. Is that correct?
    5. It looks like inPtnPres is directly associated with PresNo. Ie the inPtnPres for PresNo 2018000001 will ALWAYS be 1. There is no PresNo in your system that has more than one inPtnPresNo associated with it. Is that correct?
    6. CrDtTm looks like a child of PresNo. So the crDtTm for PresNo 2018000001 will ALWAYS be 7/8/2018. There is no PresNo in your system that has more than one CrDtTm associated with it. Is that correct?
    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

  15. #15

    Thread Starter
    Member
    Join Date
    Aug 2016
    Posts
    37

    Re: Unique records from a sql server table

    Quote Originally Posted by FunkyDexter View Post
    Could be. That would certainly make sense. Although I see a lot of coinciding values eg PresNo and InPtnPresNo which implies this is not properly normalised.

    Stop posting screenshots/images, they can be extremely difficult to read. Give us sql scripts instead. It really isn't difficult.

    Here are my questions
    1. From what I can make out in your screenshots you want a row for each PresNo, is that correct?
    2. You've selected 2018000001 as the InTrtMedID. Why? Why did you select that over, for example 2018000002?
    3. itnPtnId has the same value in all your sample data. Can we assume that's the only value that will occur for the chosen PtnId? I.e. no PtnId in your database has more than one itnPtnId associated with it?
    4. DocId looks like it's a child of PresNo. So the DocID for PresNo 2018000001 will ALWAYS be 1. For PresNo 2018000001 It will ALWAYS be 4. There is no PresNo in your system that has more than one DocId associated with it. Is that correct?
    5. It looks like inPtnPres is directly associated with PresNo. Ie the inPtnPres for PresNo 2018000001 will ALWAYS be 1. There is no PresNo in your system that has more than one inPtnPresNo associated with it. Is that correct?
    6. CrDtTm looks like a child of PresNo. So the crDtTm for PresNo 2018000001 will ALWAYS be 7/8/2018. There is no PresNo in your system that has more than one CrDtTm associated with it. Is that correct?

    Answers
    1. Yes
    2.this is unique table record id i have selected it just for a sample data (it cant be ignored)
    3.yes it may have more than on inptnid it get changes every time when patient get admitted
    4. DocID will be one for one PressNo
    5.It is associated with InPtnID if inptnid gets change for ptnid=2013000025 like if it gets new inptnid=2013000078 then
    intptnpres will start again from 1 no
    6.yes

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

    Re: Unique records from a sql server table

    2.this is unique table record id i have selected it just for a sample data (it cant be ignored)
    I'm not sure I'm reading you correctly. Did you miss-type and actually mean that it can be ignored?

    3.yes it may have more than on inptnid it get changes every time when patient get admitted
    If there was more than one value what would you expect to happen? Would it produce multiple rows (one for each inptnid) or would it produce a single row? If it's a single row, how should it decide which inptnid to show?

    5.It is associated with InPtnID if inptnid gets change for ptnid=2013000025 like if it gets new inptnid=2013000078 then
    intptnpres will start again from 1 no
    I think you're saying there could be more than one value for this. In which case I've got the same question as above. Do you want it to display a single row or multiples. If a single row, how should it decide which value to display.

    Just to clarify where I'm going with these questions: when you're grouping data you need to think about every field and how you want it to behave. Every field you Select needs to be either in the Group By or an aggregate function (eg Min, Max, Sum, Avg etc.) Basically, for each field, go through the following process:-
    1. Decide whether this field can produce multiple values. If it will only produce a single value then include it in the Group By clause. If it will produce multiple values go to 2.
    2. Decide if you want it to produce a row for each individual value or do you want it to "collapse" the values into a single row. If you want a row for each value include it in the Group By clause. If you want a single row go to 3.
    3. Define what you want to display when there is more than one value and select the appropriate aggregate function.

    You need to follow this process for each field you want to display and you need to consider it for each field individually. Start with the first field you want to display, do the above, then move to the second field and so on.
    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