How to write a function to remove duplicated characters only if they come in sequence.
Examples
darrk should return dark
eeagle should return eagle
redd should return red
corner should corner as it is as the r's are not in sequence.
I need to build a query, or use a function to removing alfabetic Character from a string.
My string is
WI14000323-0003
The value i want to keep is : 14000323-0003.
I try using this SELECT:
SELECT STUFF(Upper(z.lote), PATINDEX('%[A-Z]%', Upper(z.lote)), 2, '') from mytable
but in this case i have only 2 letters WI, but in the future i dont know if is only 2 letters, 1 letter or more, then is not what i pretend.
Hello all,
Im looking for an efficient way to remove all end of line character from a string.
Is there a function to do that or to replace them with another character?
I have a varchar field which contains some Greek characters (α, β, γ, etc...) among the regular Latin characters. I need to replace these characters with a word (alpha, beta, gamma etc...). When I try to do this, I find that it is also replacing some of the Latin characters.
DECLARE @Letters TABLE (Letter NVARCHAR(10))
INSERT INTO @Letters VALUES ('a'), ('A'), ('b'), ('B'), ('α')
SELECTLetter, REPLACE(Letter,'α','alpha')
FROM@Letters
In this case, the "α" is being replaced, but so are "a" and "A".
I have tried changing the datatype from varchar to nvarchar and also changing the collation.
I am using SSIS 2012 SP1 to import a comma delimited csv file into a SQL table.
One of the fields carries a time value:
Source = textfile, column=DT_STR(8), value format = "hhmmss", e.g. "011525"
Destination = field in SQL table, data type = time(0)
To get it from the textfile to the SQL table I am:
1.) Creating a derived column called [d_Time of Entry]with the following formula -
SUBSTRING([Time of Entry],1,2) + ":" + SUBSTRING([Time of Entry],3,2) + ":" + SUBSTRING([Time of Entry],5,2)
2.) Performing a data conversion task to convert [d_Time of Entry] from DT_STR(8) to time(0) The upload fails because values that start with a zero, i.e. times before 10am, have their leading 0's stripped before being derived. You can see this because "011525" is derived as "11:52:5" when it should be "01:15:25".
How to get the required result in SQL 2012
Create table DBInfo (Path varchar (500))
Insert into DBInfo values('/Data Sources')
Insert into DBInfo values('/Data Sources/SALES')
Insert into DBInfo values('/PRODUCTION')
Insert into DBInfo values('/PRODUCTION/SERVICE')
Insert into DBInfo values('/PRODUCTION/SERVICE/MAINTENANCE')
Insert into DBInfo values('/PRODUCTION/SERVICE/LOGISTICS')
My Expected Output
Column1,Column2,Column3
Data SourcesNullNull
Data SourcesSalesNull
PRODUCTIONNullNull
PRODUCTIONSERVICENull
PRODUCTIONSERVICEMAINTENANCE
PRODUCTIONSERVICELOGISTICS
I have a problem where I want to write a function to remove recurring characters from a string and replace them with a single same character.
For instance I have the string '12333345566689' and the result should be '12345689'. In Oracle I could do this with "regexp_replace('12333345566689', '(.)1+', '1')", but in T-SQL the only solution I could think of is something like this:
DECLARE @code NVARCHAR(255)
SET @code = '12333345566689';
SET @code = REPLACE(REPLACE(REPLACE(@Code, '1', '~1'), '1~', ''), '~1', '1');
and repeat this for 2 - 9. But I'm sure there is a more elegant version for this in SQL Server 2012.
In my staging table I am having data like below
ABL¯ABL¯0¯0¯ABL¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯
ABL¯ABQ¯480¯825¯DLS¯AMA¯ABQ¯ ¯ ¯ ¯ ¯ ¯ ¯
ABL¯ACD¯808¯1255¯DLS¯ELP¯TCS¯PHX¯ACD¯ ¯ ¯ ¯ ¯
ABL¯ADE¯1256¯471¯DLS¯AMA¯ABQ¯LSV¯ADE¯ ¯ ¯ ¯ ¯
ABL¯AFT¯1140¯1744¯DLS¯LAX¯FON¯AFT¯ ¯ ¯ ¯ ¯ ¯
ABL¯AHM¯1178¯1637¯DLS¯LAX¯AHM¯ ¯ ¯ ¯ ¯ ¯ ¯
ABL¯ALB¯1769¯1825¯DLS¯WIL¯ALB¯ ¯ ¯ ¯ ¯ ¯ ¯
ABL¯ALE¯1041¯1150¯DLS¯ALE¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯
Now I want to find the Number of times a '¯'character appears in a string. I should get output 14
I usually do this through Access so I'm not too familiar with the string functions in SQL. My question is, how do you remove characters from the middle of a string?
Ex:
String value is 10 characters long.
The string value is X000001250.
The end result should look like, X1250.
I've tried mixing/matching multiple string functions with no success. The only solution I have come up with removes ALL of the zeros, including the tailing zero. The goal is to only remove the consecutive zeroes in the middle of the string.
I'm creating a table using a XML string and a stored procedure to the SQL Server. I'm having problems in just one field, it is a nvarchar(10) type. This field's string XML Schema is also nvharchar(10) type. It is called CodCanalPrice.
Today, the only values this field can assume are AA or BB or CC, (...), ZZ or (space)A, (space)B, (...), (space)C. Note that (space) is actually a space character. They all have 2 characters.
The XML data string of one data row is showed below:
<row id="1" (...many fields...) CodCanalPrice=" A" />
When the statement is executed, SQL server removes the first space (so in this case it is updated to A without space in the table). The others values that don't have an space before like AA or BB are updated fine. All others fields are also updated correctly.
So far I tried to replace the first space for but SQL Server doesn't accept this tag.
I have a table that contains file paths as
ServernamefolderAfilenameA
ServernameFolderBFilenameB
and I need a query to return
ServernamefolderA
ServernameFolderB
I tried
SELECT DISTINCT left(Source, charindex('', Source)- 0) AS String
FROM Table
But that removes everything after the first and I need it to return all data before the last
I have data in a column that starts with 1-4 characters followed by a dash then followed by an number of characters (ex: EU-Surgery).
How do I select everything to the right of the dash when the number of characters to the left of the dash varies?
I'd like to return the left-most character from an 8 character string & the third from the left character too.
Like this ABC00123 returns AC
$query = "SELECT LEFT(uninum,3), RIGHT(uninum,5), clmarea, Date FROM tblunimov";
$result = mysql_query($query) or die(mysql_error());
echo "<div class='tblstyle1'>";
echo "<table class='tblstyle1'>";
echo "<tr><th>ini</th><th>item</th><th>area</th><th>date</th></tr>";
while($row = mysql_fetch_array($result)){
[Code] ....
Hi all,
I have one table in which one column contains duplicate values. My question is how i can use T-SQL so that i can retrive values for all columns in the table which are distinct and retriving the single value from column which contains duplicate values.
(I know distinct)
Thanks in advance.
Minesh.
Hi,
Please give the DML to SELECT the rows avoiding the duplicate rows. Since there is a text column in the table, I couldn't use aggregate function, group by (OR) DISTINCT for processing.
Table :
create table test(col1 int, col2 text)
go
insert into test values(1, 'abc')
go
insert into test values(2, 'abc')
go
insert into test values(2, 'abc')
go
insert into test values(4, 'dbc')
go
Please advise,
Thanks,
MiraJ
Dear All
This is query i have written is giving following output.
FormCodeRefCodeSerialnoDateTime
R1-196H1-68A12232138/6/2007 19:38:11
R1-196H1-68A12232138/6/2007 19:38:14
R1-205H1-67XS23124148/6/2007 19:36:08
R1-205H1-67XS23124148/6/2007 19:36:10
R1-220H1-66F433365348/6/2007 19:30:27
R1-220H1-66F433365348/6/2007 19:30:29
R1-400H1-64ER53436648/6/2007 19:24:23
R1-400H1-64ER53436648/6/2007 19:24:26
R1-408H1-65TE4626268/6/2007 19:24:23
R1-408H1-65TE4626268/6/2007 19:25:00
I want the output like this,it should take only Min Datecreated
FormCodeRefCodeSerialnoDateTime
R1-196H1-68A12232138/6/2007 19:38:14
R1-205H1-67XS23124148/6/2007 19:36:08
R1-220H1-66F433365348/6/2007 19:30:27
R1-400H1-64ER53436648/6/2007 19:24:23
R1-408H1-65TE4626268/6/2007 19:24:23
I have a table that holds the following
1 7530568 87143 OESCHD 1/5/2006 6:31:58 AM
1 7530568 87143 OESCHD 1/5/2006 7:02:36 AM
for each 7530568 ordernumber there should only be one OESCHD status.
This is the query I'm using to insert the data sent to me.
INSERT INTO ORDER_EVENTS
SELECT d.division as division,
dt.orderNum as orderNum,
dt.poNum as poNum,
dt.statusCode as statusCode,
dt.statusChangeDate as statusChangeDate
FROM dt_Order_Events dt INNER JOIN
division d ON dt.division = d.divisionShort INNER JOIN
status s ON s.division = d.division AND s.statCode = dt.statusCode
WHERE directive <> 'C' AND
dt.orderNum IN (SELECT orderNum FROM ORDER_HEADER)
This works fine when used with in the hourly transactional update. But When I ran it for the Bulk UpDate (so we'd have historical data) it allowed orders to have statuses to many times.
I am not a SQL guru, I have no idea how to write a sql statement or stored proc that will remove the duplicate records. or how to change what I have to prevent further ones.
Any help would be apreciated.
Hi There,
I am having a 1st listbox which is populating production lines, 2nd combobox to populate the production units. From here , the user will be able to select multiple production units from the combobox and hence populate the variable in listbox 3.
This is the query that I'm using to query on the variable table:
'SELECT DISTINCT PU_Id,Var_Id,Var_Desc from Variables
where PU_Id IN (@ProductionUnits)'
The problem now is that this would return all the variables for the selected production units and for those variables that have the same name, they would appear in the listbox as duplicates.
I wonder if there's any way to filter off or remove the duplicates in the listbox 3 by using sql query?
Thanks in advance for the help.
ksyong17
Hi,
I have results from a survey in a table, every entry is assigned a unique ID. I want to remove duplicate entries based on the survey data but not on the unique ID (obviously).
So far I have...SELECT DISTINCT RespondantID, Q1, Q2, Q3, Q4, Q5, Q6, Q7, Q8, Q9, Q10, Q11, Q12, Q13
FROM Results
But that gives...
1 - Anonymous
1
1
1
1
1
1
1
1
1
1
1
1
1
2 - Anonymous
2
2
2
2
2
2
2
2
2
2
2
2
2
3 - Martin
2
2
2
2
2
2
2
2
2
2
2
2
2
I.e. in the above example, it would seem that 'Martin' submitted his data once and then submitted it again with his name.
How can I remove the '2 - Anonymous' frrom the data set?
Thanks!
i've to generate a notepad using this query in vb.net :
strSql = "Select count(*), d.ShareholderId, d.UsufructId, d.BnkAccount, b.SBMCode, " & _
"LTRIM(ISNULL(d.TitleCode + ' ', '')) + LTRIM(ISNULL(d.Forename + ' ', '')) + d.Surname as ShName," & _
"d.BankCode, (select count(*) from (select ShareholderId from dividend " & _
"where CompCode = 'L1' and PaymentMode = 'B' group by ShareholderId, UsufructId, " & _
[Code]....
In the select statement i need to select "d.amount" as well. When i do so, it ask me to insert it in the group by option or in an aggregate function.
Grouping by "d.amount" returns extra field as there can be 2 similar "d.shareholderId" but with different amount.
how to group the "d.amount" without having repetition in the "d.shareholderid" ??
I am trying to get people from my table that have closed accounts. However, in my table many people have more than one account. They will have multiple closed accounts and some active accounts. I need to get the people with only closed accounts.
Values in the table
Code:
name surname status Closed Number
----------- --------- ----------- ------------- ----------------------------
Jeff Burns closed 2012/01/01 142
Tina Drewmor closed 2008/05/20 546
Jeff Burns active 1900/01/01 354
Kyle Higgin active 1900/01/01 851
Tina Drewmor closed 2009/04/14 154
The query I am using so far is:
Code:
select
d.name,
d.surname,
s.status,
s.closed,
s.number
from
d d inner join s s on d.number = s.number
where
s.status = 'closed'
What I need to see in the results
Code:
name surname status Closed Number
----------- --------- ----------- ------------ -----------------------------
Tina Drewmor closed 2008/05/20 546
Tina Drewmor closed 2009/04/14 154
I have regular work that requires me to extract a bunch of customer records from our database, and then remove duplicate address destinations (so we dont mail the same address more than once).
I can currently achieve this using a combination of my poor SQL skills and Excel, but it's really not working out for me, so looking for SQL wizardry necessary to do it just in SQL.
Relevant fields:
Member.AddressBarcode (This is a unique barcode (Text representation of a base-3 number) based on the customer address. So if there's more than one record in the pulled records with the same barcode, we then look at Member.MemberTypeID to determine whether to include this record in the results or discard it as a duplicate. Note that AddressBarcode may be blank if the mailing address couldn't be validated, if it is blank we don't discard it since there is no easy way to detect duplicate addresses without the barcode)
Member.MemberTypeID (This is the type of member account. We have 3 types - Single, Joint Primary, Joint Secondary, represented in this field by the numbers 1/2/3. This is also the order of preference of who to mail. So if there is a Joint Primary and Joint Secondary with the same mailing barcode, we want to discard the Joint Secondary from the results, so that the Joint Primary is the record we include in the results of who to mail.)
Member.ID (Unique numeric ID for each customer. Kind of irrelevant here, but it's a key)
So some pseudo code for what I'm trying to achieve is:
(Member.MemberTypeID = 1)
OR (Member.MemberTypeID = 2 AND Member.AddressBarcode not in results of Member.MemberTypeID = 1)
OR (Member.MemberTypeID = 3 AND Member.AddressBarcode not in results of Member.MemberTypeID = 2 AND Member.AddressBarcode not in results of Member.MemberTypeID = 1)
I suspect it requires some sort of join...
Hi All,
Below is a snippet of MS SQL inside some VB that retieves
Commodity info such as product names and related information and returns the results in an ASP Page. My problem is that with certain searches, elements returned in the synonym field repeat. For instance, on a correct search I get green, red, blue, and yellow which is correct. On another similar search with different commodity say for material, I get Plastic, Glass,Sand - Plastic, Glass,Sand - Plastic, Glass, Sand. I want to remove the repeating elements returned in this field. IOW, I just need one set of Plastic, Glass and Sand. I hope this makes sense.
Below is the SQL and the results from the returned page.
PS I tried to use distinct but with no luck I want just one of each in the example below.
Thanks in Advance!
Scott
==============================
SQL = ""
SQL = "SELECT B.CIMS_MSDS_NUM," & _
"A.COMMODITY_NUMBER, " & _
"B.CIMS_TRADE_NME," & _
"B.CIMS_MFR_NME," & _
"B.CIMS_MSDS_PREP_DTE," & _
"B.APVL_CDE," & _
"COALESCE(C.REGDMATLCD,'?') AS DOTREGD," & _
"COALESCE( D.CIMS_TRADE_SYNM,'NO SYNONYMS') AS SYNONYM, " & _
"A.MSDS_CMDTY_VERIF, " & _
"A.CATALOG_ID " & _
"FROM ( MATEQUIP.VMSDS_CMDTY A " & _
" RIGHT OUTER JOIN MATEQUIP.VCIMS_TRD_PROD_INF B " & _
" ON A.CIMS_MSDS_NUM = B.CIMS_MSDS_NUM " & _
" LEFT OUTER JOIN MATEQUIP.VDOT_TRADE_PROD C " & _
" ON A.CIMS_MSDS_NUM = C.MSDSNUM " & _
" LEFT OUTER JOIN MATEQUIP.VCIMS_TRD_PROD_SYN D " & _
" ON B.CIMS_MSDS_NUM = D.CIMS_MSDS_NUM) "
SQL1 = ""
SQL1 = SQL
SQL = SQL & "WHERE " & Where & " "
==================================
Here is a piece of the problem field, note repeating colors etc.
CCM-PAINTS & COATINGS (1/26/98)F65E36 ORANGE F65W1 GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEF65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65E36 ORANGE F65W1 GLOSS WHITEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65E36 ORANGE F65W1 GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65A1 WARM GRAY F65L6 TURQUOISEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65B1 GLOSS BLACK F65N11
When I create a table in sql server database name appears in prefix of table.
My database name: Digitall
My table name: Digitall.News
How can I remove Digitall prefix from my tables?
I have found a bunch of duplicate records in our housing database that ideally I need to delete.There are two tables that I need to remove data from ih_cml_log_entry and ih_cml_log_notes. There is no unique identifier between the tables for a log entry. So I have had to join on the person_ref, log_seq and the date/time of entry.How do I go about deleting the data - I've used the script below to identify what I need to delete -
SELECT *
FROM
(
select cml.person_ref, cml.open_date + open_time as 'datetime',cml.open_user,cml.log_type
,ROW_NUMBER() OVER (PARTITION BY cml.person_ref, cml.open_date + cml.open_time,cml.open_user,cml.log_type ORDER BY (SELECT 0)) AS RowNo
,n.note
FROM ih_cml_log_entry cml
[code]...
hi All,
I have to remove non-useful and duplicate records containing NULL , Blanks and extra spaces (either on left or right side of the column values) etc from all the tables in my server's DB XYZ weekly thru a a scheduled job with the help of a Stored Proc, that s i guess called Purging og DB. Plz help how i can do it with T-SQL.
Also i have to find out and remove all the duplicate DB objects(tables) from the DB .e.g. a table existing with name TABLE_TEST or TABLE_DEBUG etc for an original table TABLE , making sure no any of the base table is dropped.
Plz help me reagrding these two problems.
Thanks in advance for the quick replies.
Mohit
I am trying to figure out how I can find the names of people who have a double occurrence or more of characters either in their first name or last name.
This is for a data cleanup.
CREATE TABLE #Names ( FIRSTNAME VARCHAR (20), LASTNAME VARCHAR (20))
INSERT INTO #Names VALUES
('Thomas', 'Hohner')
,('Mike', 'SSmith')
,('TtTony', 'Hawk')
,('Jeff', 'Smith')
,('ZZSara', 'Jones')
,('Luke', 'HendersonZZ')
,('Lily', 'PPadZZZ')
SELECT * FROM #Names
DROP TABLE #Names
I need removing cursor in my table valued function with alternate code.
ALTER FUNCTION [dbo].[eufn_e5_eSM_SE_GetCurrentContentForContainer]
(
@containerSqlId SMALLINT,
@containerIncId INT
)
RETURNS @Results TABLE
[Code] ....
I am trying to insert a row into a table of Microsoft SQL Server 2000.
There are various columns.
[SNO] [numeric](3, 0) NOT NULL ,
[DATT] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[DATTA] [char] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[CODECS] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
The [DATTA] column is causing a problem. Even if I am trying to put only 1700 character string into [DATTA], the java code throws the following exception:-
StaleConnecti A CONM7007I: Mapping the following
SQLException, with ErrorCode 0 and SQLState 08S01, to a
StaleConnectionException: java.sql.SQLException: [Microsoft][SQLServer 2000
Driver for JDBC]Connection reset
at
com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
Why is it throwing an exception even though the sum-total of this row doesn't exceed 8000 characters?
Can anyone please tell me what's wrong?
Hi, Please help me with an SQL Query that fetches all the records from the three tables but a unique record for each forum and topicid with the maximum lastpostdate. Please provide separate solutions for SqlServer2000/2005.
I have four tables namely – Forums,Topics,Threads and Messages in SQL Server2000 (scripts for table creation and insertion of test data given at the end). Now, I have formulated a query as below :-
SELECT Forums.forumid AS ForumID,Topics.topicid AS TopicID,Topics.name AS TopicName,LastPosts.date as LastPostDate,LastPosts.author AS Author,
(select count(threadid) from Threads where Threads.topicid=Topics.topicid) as NoOfThreads
FROM Forums JOIN Topics ON Forums.forumid=Topics.forumid
LEFT OUTER JOIN
(
SELECT Topics.forumid,Threads.topicid,Messages.date,Messages.author FROM Topics
INNER JOIN Threads ON Topics.topicid = Threads.topicid
INNER JOIN Messages ON Threads.threadid=Messages.threadid
WHERE Messages.date=(SELECT MAX(date) FROM Messages WHERE Messages.threadid = Threads.threadid)
) as LastPosts
ON LastPosts.forumid = Topics.forumid AND LastPosts.topicid = Topics.topicid
Whose result set is as below:-
forumid
topicid
name
LastPostDate
author
NoOfThreads
1
1
Java Overall
2008-02-02 13:06:06.267
l@m.com
2
1
1
Java Overall
2008-01-27 14:46:41.000
c@b.com
2
1
2
JSP
NULL
NULL
0
1
3
EJB
NULL
NULL
0
1
4
Swings
2008-01-27 15:12:51.000
p@q.com
1
1
5
AWT
NULL
NULL
0
1
6
Web Services
NULL
NULL
0
1
7
JMS
NULL
NULL
0
1
8
XML,HTML
NULL
NULL
0
1
9
Javascript
NULL
NULL
0
2
10
Oracle
NULL
NULL
0
2
11
Sql Server
NULL
NULL
0
2
12
MySQL
NULL
NULL
0
3
13
CSS
NULL
NULL
0
3
14
FLASH/DHTLML
NULL
NULL
0
4
15
Best Practices
NULL
NULL
0
4
16
Longue
NULL
NULL
0
5
17
General
NULL
NULL
0
I want the result set as follows:-
forumid
topicid
name
LastPostDate
author
NoOfThreads
1
1
Java Overall
2008-02-02 13:06:06.267
l@m.com
2
1
2
JSP
NULL
NULL
0
1
3
EJB
NULL
NULL
0
1
4
Swings
2008-01-27 15:12:51.000
p@q.com
1
1
5
AWT
NULL
NULL
0
1
6
Web Services
NULL
NULL
0
1
7
JMS
NULL
NULL
0
1
8
XML,HTML
NULL
NULL
0
1
9
Javascript
NULL
NULL
0
2
10
Oracle
NULL
NULL
0
2
11
Sql Server
NULL
NULL
0
2
12
MySQL
NULL
NULL
0
3
13
CSS
NULL
NULL
0
3
14
FLASH/DHTLML
NULL
NULL
0
4
15
Best Practices
NULL
NULL
0
4
16
Longue
NULL
NULL
0
5
17
General
NULL
NULL
0 I want all the rows from the Forums,Topics and Threads table and the row with the maximum date (the last post date of the message) from the Messages table as shown above.
When I use the query by using only three tables namely Forums,Topics and Threads, I get the correct result. The query is as:-
SELECT Topics.forumid AS ForumID,Forums.name AS ForumName,Topics.topicid as TopicID,Topics.name as TopicName,
LastPosts.author AS Author,LastPosts.lastpostdate AS LastPost,
(select count(threadid) from threads where Threads.topicid=Topics.topicid) as NoOfThreads
FROM Forums JOIN Topics ON Forums.forumid=Topics.forumid LEFT OUTER JOIN (
SELECT Topics.forumid, Threads.topicid, Threads.lastpostdate, Threads.author FROM Threads
INNER JOIN Topics ON Topics.topicid = Threads.topicid
WHERE Threads.lastpostdate IN (SELECT MAX(lastpostdate) FROM threads WHERE Topics.topicid = Threads.topicid)
) AS LastPosts
ON LastPosts.forumid = Topics.forumid AND LastPosts.topicid = Topics.topicid
Whose result set is as:-
ForumID
ForumName
TopicID
TopicName
Author
LastPost
NoOfThreads
1
Developers
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2
1
Developers
2
JSP
NULL
NULL
0
1
Developers
3
EJB
NULL
NULL
0
1
Developers
4
Swings
p@q.com
2008-01-27 15:12:51.000
1
1
Developers
5
AWT
NULL
NULL
0
1
Developers
6
Web Services
NULL
NULL
0
1
Developers
7
JMS
NULL
NULL
0
1
Developers
8
XML,HTML
NULL
NULL
0
1
Developers
9
Javascript
NULL
NULL
0
2
Database
10
Oracle
NULL
NULL
0
2
Database
11
Sql Server
NULL
NULL
0
2
Database
12
MySQL
NULL
NULL
0
3
Desginers
13
CSS
NULL
NULL
0
3
Desginers
14
FLASH/DHTLML
NULL
NULL
0
4
Architects
15
Best Practices
NULL
NULL
0
4
Architects
16
Longue
NULL
NULL
0
5
General
17
General
NULL
NULL
0
The scripts for creating the tables and inserting test data is as follows in an already created database:-
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Topics__forumid__35BCFE0A]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Topics] DROP CONSTRAINT FK__Topics__forumid__35BCFE0A
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Threads__topicid__34C8D9D1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Threads] DROP CONSTRAINT FK__Threads__topicid__34C8D9D1
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Messages__thread__33D4B598]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Messages] DROP CONSTRAINT FK__Messages__thread__33D4B598
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Forums]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Forums]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Topics]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Topics]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Threads]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Threads]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Messages]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Messages]
GO
CREATE TABLE [dbo].[Forums] (
[forumid] [int] IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Topics] (
[topicid] [int] IDENTITY (1, 1) NOT NULL ,
[forumid] [int] NULL ,
[name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Threads] (
[threadid] [int] IDENTITY (1, 1) NOT NULL ,
[topicid] [int] NOT NULL ,
[subject] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[replies] [int] NOT NULL ,
[author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[lastpostdate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Messages] (
[msgid] [int] IDENTITY (1, 1) NOT NULL ,
[threadid] [int] NOT NULL ,
[author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[date] [datetime] NULL ,
[message] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Forums] ADD
PRIMARY KEY CLUSTERED
(
[forumid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Topics] ADD
PRIMARY KEY CLUSTERED
(
[topicid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Threads] ADD
PRIMARY KEY CLUSTERED
(
[threadid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Messages] ADD
PRIMARY KEY CLUSTERED
(
[msgid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Topics] ADD
FOREIGN KEY
(
[forumid]
) REFERENCES [dbo].[Forums] (
[forumid]
)
GO
ALTER TABLE [dbo].[Threads] ADD
FOREIGN KEY
(
[topicid]
) REFERENCES [dbo].[Topics] (
[topicid]
)
GO
ALTER TABLE [dbo].[Messages] ADD
FOREIGN KEY
(
[threadid]
) REFERENCES [dbo].[Threads] (
[threadid]
)
GO
------------------------------------------------------
insert into forums(name,description) values('Developers','Developers Forum');
insert into forums(name,description) values('Database','Database Forum');
insert into forums(name,description) values('Desginers','Designers Forum');
insert into forums(name,description) values('Architects','Architects Forum');
insert into forums(name,description) values('General','General Forum');
insert into topics(forumid,name,description) values(1,'Java Overall','Topic Java Overall');
insert into topics(forumid,name,description) values(1,'JSP','Topic JSP');
insert into topics(forumid,name,description) values(1,'EJB','Topic Enterprise Java Beans');
insert into topics(forumid,name,description) values(1,'Swings','Topic Swings');
insert into topics(forumid,name,description) values(1,'AWT','Topic AWT');
insert into topics(forumid,name,description) values(1,'Web Services','Topic Web Services');
insert into topics(forumid,name,description) values(1,'JMS','Topic JMS');
insert into topics(forumid,name,description) values(1,'XML,HTML','XML/HTML');
insert into topics(forumid,name,description) values(1,'Javascript','Javascript');
insert into topics(forumid,name,description) values(2,'Oracle','Topic Oracle');
insert into topics(forumid,name,description) values(2,'Sql Server','Sql Server');
insert into topics(forumid,name,description) values(2,'MySQL','Topic MySQL');
insert into topics(forumid,name,description) values(3,'CSS','Topic CSS');
insert into topics(forumid,name,description) values(3,'FLASH/DHTLML','Topic FLASH/DHTLML');
insert into topics(forumid,name,description) values(4,'Best Practices','Best Practices');
insert into topics(forumid,name,description) values(4,'Longue','Longue');
insert into topics(forumid,name,description) values(5,'General','General Discussion');
insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'About Java Tutorial',2,'a@b.com','1/27/2008 02:44:29 PM');
insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'Java Basics',0,'x@y.com','1/27/2008 02:48:53 PM');
insert into threads(topicid,subject,replies,author,lastpostdate) values (4,'Swings',0,'p@q.com','1/27/2008 03:12:51 PM');
insert into messages(threadid,author,date,message) values(1,'a@b.com','1/27/2008 2:44:39 PM','Where can I find Java tutorials.');
insert into messages(threadid,author,date,message) values(1,'c@d.com','1/27/2008 2:45:28 PM','Please visit www.java.sun.com');
insert into messages(threadid,author,date,message) values(1,'c@d.com','1/27/2008 2:46:41 PM','Do a Google serach for more tutorials.');
insert into messages(threadid,author,date,message) values(2,'x@y.com','1/27/2008 2:48:53 PM','Please provide some Beginner tutorials.');
insert into messages(threadid,author,date,message) values(3,'p@q.com','1/27/2008 3:12:51 PM','How many finds of layout managers are there?');
insert into messages(threadid,author,date,message) values(2,'l@m.com','2/2/2008 1:06:06 PM','Search on Google.');
I have a stored procedure that returns a single row based on a parameter of employee ID. This particular procedure uses a CTE to traverse our org structure. One of the columns is returning a delimited string of Windows login values and due to the business rules, it can contain duplicate values. I need to have that column contain only unique values - no dupes.
For example, this one column could contain something like this:
domainuser1;domainuser2;domainuser2;domainuser 3;
The need is to convert to this:
domainuser1;domainuser2;domainuser3;
I know that's a tall order.
Hi,Say I have a table Job with columns name, date, salary . I want to getthe name ,date and salary for the date when that person earned maximumsalary. I am using something likeSELECT X.name,X.date,X.salaryFROM job XWHERE X.salary IN(SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);The problem is ; if a person earns maximum salary on two dates, both ofthe dates are printed. I just want to get any one of those two rows.I triedSELECT X.name,Min(X.date),X.salaryFROM job XWHERE X.salary IN(SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);but it gives error.Can anybody please suggest a solution?Regards,Aamir
View 4 Replies View RelatedI have tested the code below to remove duplicate table entries based on the field IntOOS. This code works; however, I want to ensure I am removing the oldest entries and keeping the most recent entry. The field to key on this would be something like Max(ID). What would be the best way to ensure I keep the most recent table entry?
/*** Removes duplicate rows from ampfm.rpt_AdtVisitDiag table
by Chuck King 20070928
***/
;WITH CTE
as
(
SELECT
ROW_NUMBER() OVER (Partition By IntOOS Order BY IntOOS) AS ROWID
,DischDate
,AdmDiagCode
,ID
,AdmDiagCodeDesc
,AdmittingDiagnosis
,AnyDx
,DischDx
,ECode
,IntOOS
,PrinDxAnesDesc
,PrinDxAnesInd
,PrinDxCode
,PrinDxCondDesc
,PrinDxCondInd
,PrinDxDesc
,PrinDxEqualsAdmDxYnu
,SecDx10AnesDesc
,SecDx10AnesInd
,SecDx10Code
,SecDx10CondDesc
,SecDx10CondInd
,SecDx10Description
,SecDx11AnesDesc
,SecDx11AnesInd
,SecDx11Code
,SecDx11CondDesc
,SecDx11CondInd
,SecDx11Description
,SecDx12AnesDesc
,SecDx12AnesInd
,SecDx12Code
,SecDx12CondDesc
,SecDx12CondInd
,SecDx12Description
,SecDx13AnesDesc
,SecDx13AnesInd
,SecDx13Code
,SecDx13CondDesc
,SecDx13CondInd
,SecDx13Description
,SecDx14AnesDesc
,SecDx14AnesInd
,SecDx14Code
,SecDx14CondDesc
,SecDx14CondInd
,SecDx14Description
,SecDx15Code
,SecDx15Description
,SecDx1AnesDesc
,SecDx1AnesInd
,SecDx1Code
,SecDx1CondDesc
,SecDx1CondInd
,SecDx1Description
,SecDx2AnesDesc
,SecDx2AnesInd
,SecDx2Code
,SecDx2CondDesc
,SecDx2CondInd
,SecDx2Description
,SecDx3AnesDesc
,SecDx3AnesInd
,SecDx3Code
,SecDx3CondDesc
,SecDx3CondInd
,SecDx3Description
,SecDx4AnesDesc
,SecDx4AnesInd
,SecDx4Code
,SecDx4CondDesc
,SecDx4CondInd
,SecDx4Description
,SecDx5AnesDesc
,SecDx5AnesInd
,SecDx5Code
,SecDx5CondDesc
,SecDx5CondInd
,SecDx5Description
,SecDx6AnesDesc
,SecDx6AnesInd
,SecDx6Code
,SecDx6CondDesc
,SecDx6CondInd
,SecDx6Description
,SecDx7AnesDesc
,SecDx7AnesInd
,SecDx7Code
,SecDx7CondDesc
,SecDx7CondInd
,SecDx7Description
,SecDx8AnesDesc
,SecDx8AnesInd
,SecDx8Code
,SecDx8CondDesc
,SecDx8CondInd
,SecDx8Description
,SecDx9AnesDesc
,SecDx9AnesInd
,SecDx9Code
,SecDx9CondDesc
,SecDx9CondInd
,SecDx9Description
,VisitTypeCode
,accountnumber
,DischVisitTypeCode
FROM ampfm.rpt_AdtVisitDiag
)
--Select * From CTE
Delete From CTE Where ROWID > 1