MS SQL Server :: Querys And Duplicated Fields

TSQL : How To Delete Duplicated Rows Except The Top 1 Order By Some Fields?

Apr 15, 2004

the table is like :

ID F1 F2 F3
--- --- --- ---
1 A 1 VR
2 B 2 VR
3 A 3 VF
4 A 2 VF
5 B 1 VF
......

the rules is:
if there are rows with same values of F3 field,then choose the top 1 row order by F1,F2 in the group,and delete other rows.

how can I delete rows with ID in (2 , 3 , 5)?

View 7 Replies View Related

All Or None Querys

Jul 24, 1999

I am querying my dBase "Reservations" to obtain a range of dates for
rooms in our hotel that are available for a vacationer to reserve...

this is the my present SQL structure (two tables(roomReserve & roomDescribe inner joined)
**********************
SELECT roomReserve.availibility, roomDescribe.numRooms

FROM roomDescribe INNER JOIN roomReserve ON roomDescribe.code = roomReserve.code

WHERE (((roomReserve.availibility) Between 8/20/99 And 8/23/99) AND ((roomDescribe.numRooms)>0));

****************************
The problem is that I do not want any room open dates (roomReserve.Availibility) to come up if any of the days in between the date range have 0 rooms available (in other words no room available on that date) (roomReserve.numRooms)>0

But they do because the query is not correctly written to be an all or none recordset for the date range time peroid�

For example, if I query for the date range of 8/20/99 to 8/23/99 using a criteria that the room must be available (>0), and let's say the date 8/21/99 has 0 rooms available for the King Fireplace room, then the query's results set gives me a list of the other 3 dates (8/20, 8/22, 8/23) for when the King Fireplace room is available...

I don't want anything thing for that date range to come out if any of the dates (within that range 8/20-8/23) have a room status = 0 during that time period...

I need a way to receive an all or none recordset result... with only one query statement�

I do not want to have to query a query� if that's at all possible�

thank you... chris james

View 1 Replies View Related

Status Querys

Jun 8, 2007

I just started working with MS SQL. I'm attempting to find the equivalent of the MySQL commands STATUS and SHOW STATUS.

Essentially, I want to connect to the database from a .net app, check the status, such as how many connections, table locks, errors, etc... and display that information on my application.

Any recommendations?

View 5 Replies View Related

How To Use Two Querys In One Result Set

May 14, 2008

select *

from OPS_TimeCards tc

join OPS_TimeCardHours tch on tch.TimeCardID = tc.TimeCardID

join OPS_Projects pj on pj.ProjectID = tch.ProjectID

where tch.TimeCardID = @TimeCardID

Union

select *

from OPS_TimeCards tc

join OPS_TimeCardExpenses tce on tce.TimeCardID = tc.TimeCardID

join OPS_Projects pj on pj.ProjectID = tce.ProjectID

where tce.TimeCardID = @TimeCardID

I have this query but when i try to use the UNION i get this error

Msg 205, Level 16, State 1, Procedure GetTimeCardSearchResults, Line 24

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

I need to run both of these queries but have them return one result set....anyone know how to do???

View 5 Replies View Related

Question On Querys

Sep 26, 2007

Ok in Replication for sql 2000,'
what happens when a query such as 'update tableA set a=1 where id=5' affects 1 row. What will happen when it does not affect any row.

Aaron

View 1 Replies View Related

Tips For Speed Up Sql Querys Using Asp.net

Mar 22, 2004

Somebody can give some tips or hints for speed up my querys and procedures from sql and get more performance, what is best to use, joins o cursors, using cursors can give me more performance?

View 1 Replies View Related

2 Querys Not Returning The Same Data

Apr 17, 2008

I have two databases with table structures exactly the same. The query in one database works and returns correctly. The same query (copied and pasted) in another database returns the correct records but completely ignores the order by. Even stranger, when you are creating the query under modify and run it, it is fine. But when I right-click on the query and go to 'Open View' then the query does not return correctly. Below is the query:

SELECT TOP (100) PERCENT pf.description AS incentive, CAST(i.targetc AS FLOAT) AS targetc, i.targetp, b.basis, d.description, i.iSelect, i.direction, pf.pValue,
pf.pFigure
FROM dbo.PerfFactorWeights AS pfw INNER JOIN
dbo.PerfFactors AS pf ON pfw.incentiveID = pf.incentiveID INNER JOIN
dbo.Incentive AS i ON pfw.incentiveID = i.incentiveID AND pfw.basis = i.basisID INNER JOIN
dbo.Basis AS b ON i.basisID = b.basisID INNER JOIN
dbo.Division AS d ON pfw.division = d.uniqueID
ORDER BY incentive, d.uniqueID, b.basis

View 4 Replies View Related

Monitoring Applications Querys

Feb 1, 2007

Hi there!I'm programming in delphi and new to querieng MS SQL Server. Is there a wayto monitor the queries sent to the server and something of the returns. Atleast execution time would be interesting ...Thx in advance,Fritz

View 2 Replies View Related

Executing Querys From A Batch File

Aug 10, 2005

Someone asked me a curious question.Can I execute a query to sql server from a bat file? how?If not is there a simple scripting laguage that this person might use todrive his process that is similar to a dos bat file?

View 5 Replies View Related

Should I Add Lock Statements To My Database Write Querys

Jun 5, 2008

Hello,Could some clarify for me? I’ve been told that I should add lock statements to my database write querys (update, delete, insert). I’m not sure If it’s necessary though. The only documentation I’ve found so far is how to implement a lock statement with threading but I’m not using the threading namespace. What best practice and how would I implement this without threading or should I be using threading?Thanks in advance!

View 4 Replies View Related

Run Querys After Silent Installtion SQL 2005 Express

Mar 1, 2006

Hi,

I'm looking for a solution to run querys (create databases ed) after a silent installation of SQL 2005 Express edition (example with a .sql file). So users can run a installation unattended with all databases installed and so on.

What I got is a document about how to run a silent installation (document " Using Command Prompt Options to install SQL Server Express") but this docs there's no solution how to run querys after the installation.

Thanks

Robert-Paul

View 4 Replies View Related

SQL Server Management Studio Change Type For Querys Is Greyed Out

Jul 26, 2007

This is driving me a little nuts, I have been testing locally with SQL Server Express 2005 and this has not been a problem, however now that I have restored the databases to SQL Server 2005 (not express) When I go into SQL Server Management Studio open a table the option for changing a query type is greyed out has anyone seen this or have any ideas how I can get this working.
Thanks,
Terry

View 3 Replies View Related

SQL Server 2008 :: SELECT On Column Name From Querys Resultset In Same Query?

May 9, 2015

I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:

COLUMN_NAME Value
----------- -----
colB 123
colA XYZ

I've tried dynamic SQL to no success, probably not executing the concept correctly..Below is what I have:

CREATE TABLE myTable (colA VARCHAR(3), colB VARCHAR(3), colC VARCHAR(3))
INSERT INTO myTable (colA, colB, colC) VALUES ('ABC', '123', '0X')
INSERT INTO myTable (colA, colB, colC) VALUES ('XYZ', '789', 'X0')

;WITH cte AS
(
SELECT CAST(PATINDEX('%[^0]%', colC) AS SMALLINT) pos, STUFF(colC, 1, PATINDEX('%[^0]%', colC), '') colC
FROM myTable

[code]....

View 5 Replies View Related

Duplicated Rows

Jul 5, 2006

Hello,
I have a table T1 with fields ID, F1, F2, F3, F4, F5, F6….

I need to find if there is duplicated rows based on F1, F2, F3 columns. If there is set F5=’minimum’ where ID is MIN(ID). So the smallest should be set as minimum. How can I do this in a stored procedure?

View 1 Replies View Related

Duplicated Key.. How Can I Find This Key?

Jun 5, 2001

Hi, All
I have a problem with one table.
This table is corrupted so I drop the table and recreate the table..(Of course I export data) After that I try to put the primary key to new table but it won't allow me to do it. Error message says " There are duplicated key existed" Therefore I open up the EM and take a look at that table. There is key in that table but not Primary key..(also from the query analyzer using sp_help)
My question to U is how can I find this duplicated key and delete that info? I think somewhere in the system table contains this info but I don't know where:-(((

Thanks in advance..
Jay

View 1 Replies View Related

Duplicated Identity!? How's That Possible!?

May 27, 2004

I have a column of primary key, integer and identity. It has been working fine til 2 days ago. Though the column is IDENTITY, it duplicated number by triple! How's that possible?

Has anyone came across this problem?
If you have, how did you fix it?

Jimmy

View 2 Replies View Related

How To Insert Value Without Duplicated

Nov 12, 2004

Hello, everyone:

There is a table MyTable,
A B
-----------------
a c
b k
mk oc

How to insert from another table, YourTable,
C D
---------------
d c
e p
mk oc

without duplicate value such as third row?

Thanks a lot

ZYT

View 1 Replies View Related

Duplicated Records

Jun 9, 2008

Hi,

I have a table which has duplicated records sometimes. How can I write a query that returns only duplicated records?

Exp
Name IDTel
John12399999999
Derek54888888888
Raj798777777777
John12399999999

I need to see:
Name IDTel
John12399999999

Please help me.

Sep

View 2 Replies View Related

Duplicated Rows

Jul 5, 2006

Hello,
I have a table T1 with fields ID, F1, F2, F3, F4, F5, F6��.

I need to find if there is duplicated rows based on F1, F2, F3 columns. If there is set F5=��minimum�� where ID is MIN(ID). So the smallest should be set as minimum. How can I do this in a stored procedure?

View 4 Replies View Related

Error Duplicated Key

Jan 30, 2008

Hey,

I have a table that I truncate with an execute sql task, which uses the following statement:
truncate table dbo.tablename.

Afterwards I fill the table with data. Sometimes it gives an error that there are duplicated keys in the table, but it's truncated before the fill...
Is there anyone who has an idea on this matter?

Thanx

View 1 Replies View Related

Duplicated Rows

Oct 31, 2006

Hi,

I have just started developing in SQL Express in the last 2 months so still learning. The problem I��m having with my stored procedure is that I get duplicate rows in my results. The row is a duplicate in terms of column 'Job No' as when the query runs in access only one instance of each 'Job No' is returned but when I recreate the query in SQL server I get a number of rows back for the same 'Job No'? How would I go about getting just 1 instance of each 'Job No' back? With column 'Days to Date' showing the total 'Days to Date' for each Job No. Please see Ms Access results if unsure of what I��m asking.

A copy of the stored procedure is below and a sample of the out-put with Ms Access results at very bottom.

ALTER PROCEDURE [dbo].[sl_DaysDonePerJob] AS

SELECT CASE WHEN [Job No] IS NULL THEN '' ELSE [Job No] END AS [Job No], SUM([Actual Days]) AS [Days to Date], CONVERT(nvarchar(10),MIN(SessionDate),101) AS [Start Date],

CONVERT(nvarchar(10),MAX(SessionDate),101) AS [End Date],

MAX(CASE WHEN DATEPART(MM,SessionDate)=1 THEN 'Jan'

WHEN DATEPART(MM,SessionDate)=2 THEN 'Feb'

WHEN DATEPART(MM,SessionDate)=3 THEN 'Mar'

WHEN DATEPART(MM,SessionDate)=4 THEN 'Apr'

WHEN DATEPART(MM,SessionDate)=5 THEN 'May'

WHEN DATEPART(MM,SessionDate)=6 THEN 'Jun'

WHEN DATEPART(MM,SessionDate)=7 THEN 'Jul'

WHEN DATEPART(MM,SessionDate)=8 THEN 'Aug'

WHEN DATEPART(MM,SessionDate)=9 THEN 'Sep'

WHEN DATEPART(MM,SessionDate)=10 THEN 'Oct'

WHEN DATEPART(MM,SessionDate)=11 THEN 'Nov'

WHEN DATEPART(MM,SessionDate)=12 THEN 'Dec' END) AS 'End Month'

FROM Sessions

GROUP BY [Job No], Sessions.SessionDate

ORDER BY [Job No]

Results in SQL Server Express

'Job No' 'DaystoDate' 'Start Date' 'End Date' 'End Month'

1113-001 0 08/16/2001 08/16/2001 Aug
1113-002 0.5 07/11/2000 07/11/2000 Jul
1113-002 0.5 02/09/2000 02/09/2000 Feb
1116-001 1 07/07/1999 07/07/1999 Jul
1116-001 1 07/06/1999 07/06/1999 Jul
1118-001 1 01/12/1999 01/12/1999 Jan
1118-001 0.5 03/17/1999 03/17/1999 Mar
1118-001 1 02/23/1999 02/23/1999 Feb
1118-001 1 01/26/1999 01/26/1999 Jan
1118-001 0.5 03/09/1999 03/09/1999 Mar
1118-001 1 12/15/1998 12/15/1998 Dec
1118-001 1 02/09/1999 02/09/1999 Feb

Results in Ms Access

Days Done per Job

JobNo
Days to Date
Start Date
End Date
End Month

1113-001
0.00
16/08/2001
16/08/2001
Aug01

1113-002
1.00
09/02/2000
11/07/2000
Jul00

1116-001
2.00
06/07/1999
07/07/1999
Jul99

1118-001
6.00
15/12/1998
17/03/1999
Mar99

View 2 Replies View Related

Delete One Of The Duplicated Row

Nov 4, 2006

I have many data in a table in which some rows are duplicated. How can I, for all duplicated rows, delete the extra rows and leave only one? You may assume checking one column is enough to tell if a row is duplicated.

Thanks

View 6 Replies View Related

Find Duplicated Value

Oct 4, 2006

I have a table having 50000 rows. One field(Name) of the table may have duplicated data. How can I get all the duplicated data?

View 1 Replies View Related

Duplicated Data

Apr 16, 2008

I have Account Table with the following columns.

ACCOUNT_NUMBER (p-key)
ROUTE_NUMBER
NAME
STATE

I have a unique account number for every record in the table (hence the p-key) however I have route numbers that are duplicated in the table. How do I find all records that have a ROUTE_NUMBER duplicated.

In other words, how do I query records where the route number occurs more than one time in the table.

I would like the following results....

ACCOUNT NUMBER ROUTE NUMBER NAME STATE
12345 1 WMT NY
48734 1 CBS TX
3945857 1 NBC LA

2847 2 FDX GA
94875 2 TH PR

View 4 Replies View Related

Removing Duplicated Records

Dec 2, 2005

Hi,

Need to remove the duplicated rows from a table which has text/ntext/image type columns. The table does not have any PK/Unique column. (I accept its a bad data model). But currently changing the data model is not possible. Hence doing changes in application.

I couldn't do 'SELECT DISTINCT * from table', since the table has text columns. Though there is no PK constraint, If I know that col1 and col2 are join PKs in the table, Is that possible to select the distinct rows from such a table.

Please advise,

Thanks,
MiraJ

View 4 Replies View Related

Deleting Duplicated Rows

May 23, 2004

Hi,
I have a table named "std_attn", where, by some bad coding, lots of duplicated rows have been created. And the table don't have any PK. So Now tell me the way to remove the duplicaies..................

thnx

View 14 Replies View Related

Duplicated ID Merge In A Table

Oct 9, 2007

I am currently working to solve this problem that i am facing.
I just cant' get my desired solution to my problem.
As per below is the current db view.
emp_id skill level years remarks
2541COMPUTERBAIK1<null>
2541WORD PROCESSINGBAIK1<null>
2541EXCEL BAIK1<null>
2541POWERPOINTBAIK1<null>

how do i get this repeated emp_id to have a view of..

emp_id skill level
2541COMPUTER.wordprocessin,excel,powerpointBAIK,baik,baik,baik
years remarks
1,1,1,1 <null>,<null>,<null>,<null>

i just can't seem to get this comin up.. please kind advise
thanks all!

View 1 Replies View Related

Updating Duplicated Values

Feb 14, 2008

I have a table like that:

ColumnA ColumnB ColumnC
-------------------------------
Alice Lukas Alice.Lucas
James Redford James.Redford
James Redford James.Redford
Michael Jackson Michael.Jackson
John Brown John.Brown
John Brown John.Brown
John Brown John.Brown
George Gotham George.Gotham

I want to update duplicated values at ColumnC like:

Alice Lukas Alice.Lucas
James Redford James.Redford
James Redford James.Redford1
Michael Jackson Michael.Jackson
John Brown John.Brown
John Brown John.Brown1
John Brown John.Brown2
George Gotham George.Gotham

How can i do it?

Thanks in advance!

Note: Table is for creating email aliases from names...

View 2 Replies View Related

HOw Can Avoid Duplicated Records

Feb 27, 2008

HI EveryBody,

I have 2gb Text file to transfer the data into Our database through oledb destination

Here my prob is in the text file some duplicate records is there how can i avoid those records

plz help me

View 4 Replies View Related

Duplicated Data Display

Mar 5, 2006

Hi all. i have the following function below,which use to retrieve the order detail from 2 table which are order detail and product. i have many duplicated order id in order detail, and each order id has a unique product id which link to product to display the product information. however when i run the following function below . its duplicated each product info and dsplay in the combo box. May i know whats wrong with my code?

    Public Sub ProductShow()

        Dim myReader As SqlCeDataReader
        Dim mySqlCommand As SqlCeCommand
        Dim myCommandBehavior As New CommandBehavior
        Try
            connLocal.Open()
            mySqlCommand = New SqlCeCommand
            mySqlCommand = connLocal.CreateCommand
            mySqlCommand.CommandText = "SELECT * FROM Product P,orders O,orderdetail OD WHERE OD.O_Id='" & [Global].O_Id & "' AND P.P_Id=OD.P_Id "
                      myCommandBehavior = CommandBehavior.CloseConnection
            myReader = mySqlCommand.ExecuteReader(myCommandBehavior)
            While (myReader.Read())
                cboProductPurchased.Items.Add(myReader("P_Name").ToString())
            End While
            myReader.Close()
        Catch ex As Exception
            MsgBox(ex.ToString)
        Finally
            connLocal.Close()
        End Try

    End Sub

View 1 Replies View Related

SQL Server 2012 :: Get First Occurrence Value When Value Is Duplicated

Oct 16, 2015

generate output as specified below. i need to get 1st value when record is duplicated in hierarchyval column

IF OBJECT_ID('tempdb..#test') IS NOT NULL
drop table #test
create table #test
(
hierarchyid int
,hierarchyval int

[code]...

View 3 Replies View Related

How To Determine The Unique IDs Of Duplicated Records

Feb 21, 2006

> This is a common problem with some solution[color=blue]>>[/color]/************************************************** *********************************** Problem:* Determine the Duplicated Records in a table using single SELECT.** We shall be using Northwind database, add some duplicate records.** Here we want to know if 2 columns (CompanyName,* PHone) are duplicated in a table.*** ShipperID CompanyName Phone* ----------- ------------------------- ------------------* 1 Speedy Express (503) 555-9831* 2 United Package (503) 555-3199* 3 Federal Shipping (503) 555-9931* 4 Federal Shipping (503) 555-9931* 5 Speedy Express (503) 555-9831* 6 Federal Shipping (503) 555-9931***************************************************** **/================================================== SOLUTION 1: Gives me the IDs that are duplicated.================================================== SELECTShipperID, CompanyName, PhoneFROMSHIPPERSWHEREEXISTS (SELECTNULLFROMSHIPPERS bWHEREb.CompanyName = SHIPPERS.CompanyNameAND b.Phone = SHIPPERS.PhoneGROUP BYb.CompanyName, b.PhoneHAVINGSHIPPERS.ShipperID < MAX( b.ShipperID ))/* ********************* Output results********************/ShipperID CompanyName Phone----------- ----------------------------------------------------------------1 Speedy Express (503) 555-98313 Federal Shipping (503) 555-99314 Federal Shipping (503) 555-9931(3 row(s) affected)================================================== ===========SOLUTION 2: Gives me the data which are duplicate butnot the IDs================================================== ===========SELECTCompanyName, PhoneFROMSHIPPERSGROUP BYCompanyName, PhoneHAVINGCOUNT(*) > 1/* ********************* Output results********************/CompanyName Phone---------------------------------------- ------------------------Speedy Express (503) 555-9831Federal Shipping (503) 555-9931(2 row(s) affected)

View 1 Replies View Related