Results 1 to 15 of 15
  1. #1
    Join Date
    Jul 2007
    Posts
    13

    Unhappy Unanswered: Distinct & eliminate duplicate data

    Hi there.. Although i know my questions will sound quite stupid, but i've tried n tried n i can't get the desired output.. so here goes...

    I'm using SQL Server 2000. I have one table call SMSReceived. in that table, there's a lot of columns. But i need to display only 2 column which is column Sender and column Message. Since this is our project, we had a lot of data with the same number in the column Sender. I want to display the data but I only want the Sender number only once. (no redundant data) So i try my query like this :

    select distinct Sender from SmsReceived <-- this works since they are no redundant data

    then i tried 2 columns which is :

    select distinct Sender, Message from SmsReceived

    then, but the Sender number still redundant.. It's like distinct is not functioning. I think it is because of the different message.

    So, can anyone help me with this?.. I need to display 2 columns but only one Sender number. you can eliminate either one data as long as the Sender number didn't appear twice.

    Erm... i tried group by, but an error occured like this..

    Column 'Message' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    Sender, Message
    1234, hi how are you?
    1234, r u ok?
    7777, my name is
    7777, hello

    yeah, it is something like this.. But i need to eliminate either one...

    So the result will become like this..

    1234, hi, how are you?
    7777,hello

    Is it possible?.. Please help me...

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select Sender, min(Message) as lowest_message
      from SmsReceived
    group by Sender
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2007
    Posts
    13
    thanks..it already works...

    select dnyFrom as 'SMS Sender', min(dnyReason) as 'Reason', min(dnySMSText) as 'SMS Text', min(dnycustomerid) as 'Customer Id'
    from shldnysmsreceived
    where dnycustomerid='PRM' and
    dnycustomerid is not null and
    dnyDateReceived between '2006-12-01 00:00:00.000' and '2007-07-01 00:00:00.000'
    group by all dnyFrom
    order by min(dnySMSText) asc

    and one more thing... for example if i had data that looks like this

    1234, successful
    1234, invalid message
    4444, winner of prize
    4444, successful
    4444, invalid format
    6666, hello
    6666, bulk sms

    i need to display only the data with 'successful'. so the output will look like this

    1234, successful
    4444, successful
    6666, hello <--- can choose either one (hello or bulk sms)

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the solution i gave you was for two columns, one for the GROUP BY and the other for the MIN() function

    you do realize that by having multiple MIN() expressions, the resulting values are not necessarily from the same row, don't you?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2007
    Posts
    13
    No, i don't know that... why we can't have multiple expression? Can u explain by giving examples? .. I really dont understand

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh, you can have multiple MIN() expressions, that's not the problem

    example:

    tom 12 ggg 105
    tom 11 hhh 108
    tom 15 xxx 104
    bob 16 bbb 112
    bob 13 ccc 109
    bob 17 aaa 137

    assume GROUP BY on the first column, with MIN on the others, the resuts are:

    tom 11 ggg 104
    bob 13 aaa 109

    see? good results from the query, but these do not represent actual rows from the table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jul 2007
    Posts
    13
    owh.. so how am i going to choose 'successful' only from other data? i don't know how to use if statement..

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you would probably use a CASE expression

    but i really do not understand what you are trying to do

    your example data uses two columns, but your query has four
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jul 2007
    Posts
    13
    owh.. i was just trying to make it easy for the readers to understand.. the actual data have 4 or more columns that needed to be displayed. but for the moment, i only need to know how to display 2 columns with conditions that already stated above... and i don't know how to use CASE..

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if i give you the solution for only two columns, it will be of very little use to you...

    ... so i won't, sorry

    for an example of the CASE expression, see http://www.sqlzoo.net/fun_case
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jul 2007
    Posts
    13
    Thanks everyone..I already solve it..using case...

  12. #12
    Join Date
    Jul 2007
    Posts
    13
    hi.. i have some query that i want to solve. i had a data that looks like this. (it's actually a text messages where people sms in to win prizes) and it have to start with 'PRM'

    SMSReceived <-- column name
    Prm 65719471
    Prm 68516237
    PRM 72847410
    Prm 75031193
    PRM (SPACE) PINSEND TO32080
    prm (space)36398226
    PRM < > 22733564
    Prm < 82848916 > pin
    PRM <36943554>

    how can i choose only the number? so the desired output will look like this..
    65719471
    68516237
    72847410
    75031193
    32080
    36398226
    22733564
    82848916
    36943554

    please help..

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    While most SQL engines can do this kind of text manipulation, the SQL standard doesn't provide any standard way that I know to acomplish this. In other words, you can use the vendor specific extensions to get this done, but I don't know of anything in the SQL language definition that will give you a generic solution that will work on any SQL implementation.

    I'd post this question in the appropriate vendor/engine specific forum to get an answer (if I didn't already know how to solve it using the engine of choice).

    -PatP

  14. #14
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    I would recommend REGEXP in MySQL but i know you're not using MySQL. I'm sure there is a MSSQL equivalent though. More importantly I think you should be stripping this information down before it goes into the database...

    ever thought that someone can SQL inject from a text message?

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Isn't this exactly why your answer should be classed as incorrect if you don't follow a set pattern.
    E.g. "Text PRM then a space then your answer to 55555"
    If your results were always consistent it'd be easy as pie to extract just the number part
    George
    Home | Blog

Posting Permissions

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