Code:
select * from ( select y.mobileno,y.message,y.language,y.place,y.category,y.date,y.year from yourTable as y INNER JOIN (select mobileno, count(mobileno) from yourTable having count(mobileno)=1) as x ON
azimmer
Joined: Jul 31 '07
Location:
-
The code in my previous post should be almost OK except that it omits all duplicate mobileno-s. The one that meets your format in the post (i.e. the one with dashes) is as follows:
... -
Would it be possible to modify the data structure? I mean the first thing I would need is a running count column for each row. Do you use 2005 or 2000? (2005 has a new RANK() function that can be used to this end w/o having to modify the data structure.) BTW: Does "Time" contain dates as well? (So that we can handle wrap-arounds.)
Then: I suppose from the nature of the data that ClientIP/ServerIP combinations can "come...Leave a comment:
-
Do you want the unique mobile numbers only (i.e. ones that show up exactly once in your table), or do you want to do something with the duplicated ones (e.g. show the first or last record)?
One solution to the first goes along the following pattern:
...Code:select y.mobileno,y.message,y.language,y.place,y.category,y.date,y.year from yourTable as y INNER JOIN (select mobileno, count(mobileno) from yourTable
Leave a comment:
-
Use the TOP keyword:
...Code:declare @abc varchar(2000) set @abc = (select [B]TOP 1[/B] Sql_Table_Name from temptable10) print @abc
Leave a comment:
-
If you always have both quotes in the string, you can try the following:
...Code:SELECT left(right(myCol,len(myCol)-1),len(myCol)-2) FROM temp2
Leave a comment:
-
It may seem too trivial but isn't the result what you want if you use "WHERE p.Status=2" instead of "HAVING p.Status=2"?
It it isn't, please make sure your example is well aligned and the right headers are in place because it's difficult to make out which column is which. Also please clarify what you mean by "the last result which are conform to the condition" (i.e. last by what condition?)...Leave a comment:
-
Funny, I've just tried and it works fine for me in a made-up example. Can it be that your WHERE clause (and/or the data set) also changed since you tried with the old syntax? (Perhaps parenthesis were at other places?) You can try with an empty WHERE clause and sort out if you have the rows......Leave a comment:
-
This is a good place to start with helps: http://technet.microso ft.com/en-us/library/aa299742(SQL.80 ).aspx...Leave a comment:
-
Yes it is but far from trivial. If you use SQL Server 2005 you can use the PIVOT functionality ( http://technet.microso ft.com/en-us/library/ms177410.aspx ); either way the following page is worth reading: http://www.mssqltips.c om/tip.asp?tip=937...Leave a comment:
-
How would you know (if you'd do it by hand) which row to delete from the other table?...Leave a comment:
-
OK -- but this one is going to be longer.
NB: Person profile is basicly a switching table; these are not normally populated from existing data (because they cannot be). In your case it is possible, though.
Some of the data you need to insert is in the PERSON table, some in the BADGE table, and -- in a somewhat tricky way -- some will be constants (namely the profiles codes).
In the first step, let's join...Leave a comment:
-
Hi, I'll try to be short (for now, that is :)). Let me try to rephrase what you seem to want; tell me if it's right or wrong.
You want to fill the PERSON_PROFILE table with INSERTs in such a way that:- all persons from the PERSON table get inserted (as Person_Profile_ ID)
- Profile_Code is 'Stcy' or 'Eval' based on the person's existence/non-existence in the BADGE table (as Assigned_person )
- Profile_Assign_ Date is Date_Issued from
Leave a comment:
-
You mean it's like this:
Product Productgroup
A Something
... ...
B A
... ......Leave a comment:
-
You have a couple of choices:- Make A a FUNCTION instead of a PROCEDURE. (It's OK in your example but it has limitations in general; not that strict, though.)
- Introduce a new variable in PROCEDURE B, such as @X, EXECUTE A against @X, then SET @B=@B+@X
Hope it helps....Leave a comment:
-
According to the rules of the Forum I shall not give you a complete solution, as this looks very much like an assignment. However, I give you hints:
1. The general format of INSERTing values from one table into another is:
2. Any column in a SELECT statement (even inside an INSERT!) can be a constant or any...Code:INSERT INTO dest_table SELECT myColumn1, myColumn2, ... FROM source_table
Leave a comment:
-
...Code:SELECT productname FROM product_tbl WHERE productname=productgroup
Leave a comment:
-
It may or may not be: if your date's fractional seconds are not zero it is actually larger... Also, make sure your system interpretes dates the same way you do: 06/10 may be June 10th or October 6th depending on settings; thus I suggest you enter where clauses in ISO format: '2007-06-10 12:41:02' for June 10th. (It is possible that date you see and date you enter by hand do not follow the same syntax.)...Leave a comment:
-
...Code:SELECT a.a_value, COUNT(c.*) FROM a INNER JOIN b ON a.a_id=b.b_id LEFT OUTER JOIN c ON b.c_id=c.c_id GROUP BY a.a_valueLeave a comment:
-
A couple syntax corrections in bold:
...Code:[B]declare @strMain varchar(100) declare @Id varchar(100) declare @Cd varchar(100) declare @startdate varchar(100) [/B] set @strMain = 'Select * from C_Eng with(nolock) Where ' [B]declare [/B]@strId varchar(100) Set @strId = '' [B]declare [/B]@strCd varchar(100) Set @strCd = '' if @Id is not null and rtrim(ltrim(@Id)) <> '' set
Leave a comment:
-
Hi, I guess you need two modifications:
1. Data set: I belive that the closing tag for <PartNumber> is misplaced, move them to behind each </Price> tags like this:
...Code:<PartNumber code="123" quantity="96"> <Price unit_price="13309.00" total_amount="1277664.00"></Price>[B]</PartNumber>[/B] <PartNumber code="345" quantity="24">
Leave a comment:
No activity results to display
Leave a comment: