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.
1234, hi how are you?
1234, r u ok?
7777, my name is
yeah, it is something like this.. But i need to eliminate either one...
select dnyFrom as 'SMS Sender', min(dnyReason) as 'Reason', min(dnySMSText) as 'SMS Text', min(dnycustomerid) as 'Customer Id'
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, invalid message
4444, winner of prize
4444, invalid format
6666, bulk sms
i need to display only the data with 'successful'. so the output will look like this
6666, hello <--- can choose either one (hello or bulk sms)
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..
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).
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?
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