What I want to do is select only records from my Orders table that have repeating values in the ORDREF column.
If the order method "web" repeats 3 or more times for any customer, I want to show it.
use ws_data
select company,invno,invdte, ordref from Orders
where ordref like 'web%' or ordref like 'dir%'
order by company, invdte
TIA
ck
Any help on how to get rid of repeating records
View 1 Replies View Relatedhello i'm using asp.net 2 with VB and i have a table in my db the problem is that table has a repeated records like this imagenow i want to update the absence record but cos it's repeated it will only update one record what i want is to apply the update on all other records ? hope u can help me .thanks
View 6 Replies View RelatedI am querying several tables and piping the output to an Excel spreadsheet.
Several (not all) columns contain repeating data that I'd prefer not to include on the output. I only want the first row in the set to have that data. Is there a way in the query to do this under SQL 2005?
As an example, my query results are as follows (soory if it does not show correctly):
OWNERBARN ROUTE DESCVEHDIST CASE
BARBAR TRACKING #70328VEH 32832869.941393
BARBAR TRACKING #70328VEH 32832869.941393
BARBAR TRACKING #70328VEH 32832869.941393
DAXDAX TRACKING #9398VEH 39839834.942471
DAXDAX TRACKING #9398VEH 39839834.942471
DAXDAX TRACKING #9398VEH 39839834.942471
TAXTAX TRACKING #2407 40754.391002
TAXTAX TRACKING #2407 40754.391002
TAXTAX TRACKING #2407 40754.391002
I only want the output to be:
OWNERBARN ROUTE DESCVEHDIST CASE
BARBAR TRACKING #70328VEH 32832869.941393
DAXDAX TRACKING #9398VEH 39839834.942471
TAXTAX TRACKING #2407 40754.391002
Thanks,
Walt
I have some data in the following format;
MYTABLE
DOC_NO // REV_NO // FILE_NAME
ABC123 // A // abc123.pdf
ABC123 // B // abc123_2.docx
ABC124 // A // abc124.xlsx
ABC124 // A // -
ABC125 // A // abc125.docx
ABC125 // C // abc125.jpg
ABC125 // C // abc125.docx
ABC125 // C // -
ABC126 // 0 // -
ABC127 // A1 // abc127.xlsx
ABC127 // A1 // abc127.pdf
I'm looking to select all rows where the DOC_NO and REV_NO appear only once.(i.e. the combination of the two values together, not any distinct value in a column)
I have written the sub query to filter the correct results;
SELECT DOC_NO, REV_NO FROM [MYTABLE]
GROUP BY DOC_NO, REV_NO
HAVING COUNT(*) =1
I now need to strip out the records which have no file (represented as "-" in the FILE_NAME field) and select the other fields (same table - for example, lets just say "ADD1", "ADD2" and "ADD3")
I was looking to put together a query like;
SELECT DOC_NO, REV_NO, FILE_NAME, ADD1, ADD2, ADD3 FROM [MYTABLE]
WHERE FILE_NAME NOT LIKE '-' AND DOC_NO IN
(SELECT DOC_NO, REV_NO FROM [MYTABLE]
GROUP BY DOC_NO, REV_NO
HAVING COUNT(*) =1)
But of course, DOC_NO alone being in the subquery select is not sufficient, as (ABC125 /A) is a unique combination, but (ABC125 /C) is not, but these results would be pulled in.
I also cannot simply add an additional "AND" clause on its own to make sure the REV_NO value appears in the subquery, because it is highly repetitive and would have to specifically match the DOC_NO)
What is the easiest way of ensuring that I only pull in the records where both the DOC_NO and REV_NO (combination) are unique, or is there a better way of putting this select together altogether?
The data as it's recorded in the SQL table looks like this:
IDbatchdate_timecompany_namesymbolscore
4670713/11/14 9:30 AMJ.C. PENNEY COMPANY INC.JCP97
4670813/11/14 9:30 AMZOGENIX INCZGNX97
4670913/11/14 9:30 AMEXCO RESOURCES INCXCO93
4671013/11/14 9:30 AMMARVELL TECHNOLOGY GROUP LTDMRVL91
4671113/11/14 9:30 AMCABOT OIL & GAS CORPCOG90
[code]....
Basically what I'm after, I need to pull only the top 3 records based on score in descending order BUT these should be grouped by batch. For example, I need to select the top 3 records which have the highest score from batch 1, 2, 3, etc. where there could be any number of records in a batch and any number of batches.
Hi
I am working this stored procedure that retrieve the name of person and also the last day he did a report.
I only want to display people who had a date for their report.
I came up with this stored procedure that works but is not very optmized.
Do you have any idea of how I can optmize this stored procedure?
SELECT Tuserproject.etridperson, Tperson.name, Tperson.email,
(SELECT TOP 1 [day]
FROM TDailyReport
WHERE project = 7 AND TDailyReport.people=Tuserproject.etridperson
ORDER BY [day] DESC) AS lastday
FROM Tuserproject INNER JOIN
Tperson ON Tuserproject.etridperson = Tperson.idperson INNER JOIN
TDailyReport ON Tuserproject.etridperson = TDailyReport.people
WHERE (Tuserproject.etridproject = 7) AND (Tperson.login IS NOT NULL) AND
((SELECT TOP 1 [day]
FROM TDailyReport
WHERE project= 7 AND TDailyReport.people=Tuserproject.etridperson
ORDER BY [day] DESC) IS NOT NULL)
GROUP BY Tuserproject.etridperson, Tperson.name, Tperson.email
ORDER BY Tperson.name
Thanks
Hello
Im searching for a solution to set all matrix row or cell the same height.
it schoud looks like this example:
This is a simple matrix
test a
text b
text c
text d
text e
text f
text g
This is a matrix with all the same row-height.
test a
text b
.
text c
.
.
text d
text e
text f
text g
.
.
Thx you a lot
Dear All
I need to cerate a SP that SELECTS all the records from a table WHERE the first letter of each records starts with 'A' or 'B' or 'C' and so on. The letter is passed via a parameter from a aspx web page, I was wondering that someone can help me in the what TSQL to use I am not looking for a solution just a poin in the right direction. Can you help.
Thanks Ross
How do I:Select f1, f2, f3, from tb1 where f1=Select f1 from tb2 where f1='condition'?
View 3 Replies View RelatedSELECT Top 10 Name, Contact AS DCC, DateAdded AS DateTimeFROM NameTaORDER BY DateAdded DESC
I'm trying to right a sql statement for a gridview, I want to see the last ten records added to the to the database. As you know each day someone could add one or two records, how can I write it show the last 10 records entered.
I have an unusual problem. I am using VB.Net 2003 and sqlexpress using .NET dataset to insert records into an timecards table. After inserting several records I tried a 'Select * from timecards' and the inserted records where not selected. if I 'select * from timecards order by employee' ( or any other field) the inserted records are selected! The table was created by an Access Upsize command.
Any suggestions?
Thanks!
GordonG
I have a resultset that looks something like this:
Anzahl users_statdata_hobbies
---------------------
499 Andere
266 Essen
60 Essen,Andere
127 Essen,Musik
10 Essen,Musik,Party,Andere
30 Essen,Party
4 Essen,Party,Andere
51 Kunst
4 Kunst,Andere
13 Kunst,Essen
4 Kunst,Essen,Andere
I get this with this query which might be altered somehow:
SELECT COUNT(*) AS Anzahl, users_statdata_hobbies
FROM vgetAuswertung2
GROUP BY users_statdata_hobbies
ORDER BY users_statdata_hobbies
Of course this is not normalized but I can't change this.
Nevertheless I need to get the full number of each Hobby and not only the combination of them.
So instead or in addition to the existing recordset I need e.g
357 Essen which ist the sum of all records containing 'Essen' in the above example
The list of individual hobbies is defined therefor I could loop through the list manually and search for 'WHERE Hobbies LIKE '%ESSEN%' and count but since it's quiet a big resultset and there are several other similar tasks already I'm looking for a more performant way and I'm sure it could be done in SQL directly.
Any ideas someone?
I have a stored procedure that selects the unique Name of an item from one table.
SELECT DISTINCT ChainName from Chains
For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.
SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName
Each row of the result set returned by the stored procedure would contain:
ChainName, Array of StoreNames (or comma separated strings or whatever)
How can I code a stored procedure to do this?
Hi,I have a query like this :SELECTx1,x2,( SELECT ... FROM ... WHERE ...UNIONSELECT ... FROM ... WHERE ...) as x3FROM ...WHERE ...The problem is that I don't want to return the results where x3 isNULL.Writing :SELECTx1,x2,( SELECT ... FROM ... WHERE ...UNIONSELECT ... FROM ... WHERE ...) as x3FROM ...WHERE ... AND x3 IS NOT NULLdoesn't work.The only solution I found is to write :SELECT * FROM((SELECTx1,x2,( SELECT ... FROM ... WHERE ...UNIONSELECT ... FROM ... WHERE ...) as x3FROM ...WHERE ...) AS R1)WHERE R1.x3 IS NOT NULLIs there a better solution? Can I use an EXISTS clause somewhere totest if x3 is null without having to have a 3rd SELECT statement?There's probably a very simple solution to do this, but I didn't findit.Thanks
View 7 Replies View RelatedHi, I am trying to use a formView with an update button to update individual records in an sql database. (when i click update it doesnt perform the update and just refreshes the page. ) One of the fields in my records is a NULL - this is also one of the fields that i need to update. When i manually go into the database and enter some data, and then go back to my form, it updates fine, but as soon as i delete the data from the field, it returns to NULL and im back to square one. Any Ideas on how to get around this problem?THanks
View 4 Replies View RelatedI'm hoping someone can help me with my problem.
I'm reading in records from a 'flat file' and loading them into sql.
I have 5 values I'm loading in. I first check my sql db, if all 5 values match a current record in sql, I don't want to load the record, because it's already there. If it doesn't exist, I need to load it.
It works fine as long as none of my values are NULL. But if I have a record with a field, say Gender, that is null, if its a new record it loads fine, setting gender to NULL in sql. But then when I encounter another record, which is identical, I'm testing to see if it already exists by doing a 'select where Gender = @Gender' and it always returns that the record does not exist in the db - even though it exists (because its using the = instead of is null)!
I need some mechanism where if the value is null it tests 'Gender is null' but if there is a value, it tests gender=gender, [and I need this for all my parameters]. Or is there some other way to do this?
Here is my code for looking to see if the record exists:
public static int ExistInsured(int CaseID, object InsuredLastName, object DateOfBirth, object CurrentAge, object Gender)
{SqlConnection conn = new SqlConnection(connStng.ToString());
SqlCommand cmd = new SqlCommand("SELECT top 1 InsuredID from Insured Where PolicyID = (Select PolicyID from Policy where CaseID = @CaseID) and LastName = @InsuredLastName and DateofBirth = @DateofBirth and Gender = @Gender", conn);
int result = -1;
cmd.CommandType = CommandType.Text;cmd.Parameters.AddWithValue("@CaseID", CaseID);
cmd.Parameters.AddWithValue("@InsuredLastName", InsuredLastName);cmd.Parameters.AddWithValue("@DateOfBirth", DateOfBirth);
cmd.Parameters.AddWithValue("@CurrentAge", CurrentAge);
cmd.Parameters.AddWithValue("@Gender", Gender);
conn.Open();
try
{object ret = cmd.ExecuteScalar();
if ((ret != null) && (ret is int))result = (int)ret;
}
finally
{
conn.Close();
}return result;
}
I have data that looks like below (columns are Timestamp, Offered, Answered and Delay). I'm looking to exclude returning records that have a value for Delay that are within the top 10% of values of that column. Are there any 2005 tricks where this can be accomplished in a simple statement?
2008-02-18 08:30:002322173
2008-02-18 08:45:002120174
2008-02-18 09:00:002425230
2008-02-18 09:15:002828277
2008-02-18 09:30:002522159
Here's what I'd like to be able to do: I have a queue that holds any number tasks, so something like this here:
queue_1 task_a
task_b
task_c
task_d
Workers are assigned to teams, Red team, Blue team, Green team. What I need to do is identify instances where all tasks for a given queue have been handled by one team. Once a task has been assigned to a queue any team can work on it, but when only one team has completed every task in a queue a bonus should be awarded.
I'm looking for this to award a bonus:
queue_num task_num team
queue_1 task_a red
task_b red
task_c red
task_d red
No bonus for any team here
queue_num task_num team
queue_1 task_a red
task_b blue
task_c red
task_d green
So the red team earns a bonus. Now, I have thousands of queues each containing any number of tasks. Using T-SQL how can I find all queues where only one team was responsible for completeing every task assigned to the queue? Do I have to use a cursor and eval each task coming through or is there a faster, more efficient way to handle this in SQL?
HiI'm migrating from Access til MySQL.Works fine so far - but one thing is nearly killing me:I got the count of total records in a variabel - (antalRecords)I got the count for the Field Q1 where the value value is = 'nej'Now I just need to calculate how many % of my records have the value 'nej'I access this worked very fine - but with MySQL ( and ASP) I just cant getit right!!! I go crazy ....My code looks like this :strSQL="SELECT COUNT(Q1) AS Q1_nej FROM Tbl_evaluering " &_"WHERE Q1 = 'NEJ' "set RS = connection.Execute(strSQL)antal_nej = RS("Q1_nej")procent_nej = formatNumber((antal_nej),2)/antalrecords * 100Hope ...praying for help ...Please ;-)best wishes -Otto - Copenhagen
View 3 Replies View RelatedHi, I am copying records in a table. The source table and the target table are the same. I need the value from the id-field from both the source and target row. Is there a way to do this with one query?
I tried the following, but it doesn't seem to work:
INSERT tableOne (value1, value2, value3)
OUTPUT source.id, inserted.id
SELECT value1, value2, value3 FROM tableOne AS source
WHERE ID = @number
A basic select statement to be used.
I have a question about combining multiple records with same ID but different values between records on different fields into a single record with all values combined for all fields. Example: Multiple records with the same record ID:
RECORD_ID BO_ID Code Code_Date SubMsgCD1 SubMsgCD2 LNMsgCD1
1380900 XZ01 12 1/6/2015 P302
1380900 XZ01 12 1/6/2015 L405
1380900 XZ01 12 1/6/2015 P302 1004
INTO a single record:
RECORD_ID BO_ID Code Code_Date SubMsgCD1 SubMsgCD2 LNMsgCD1
1380900 XZ01 12 1/6/2015 P302 L405 1004
Using MSSQL 2012
I have a simple select query and I need to eliminate records whose values from 2 different fields match. I thought I had this working, but if one of those fields in my data IS NULL it filters out those records. If I comment out my last line then my number record shows, if I include that statement that record drops. The only thing I see in my data is the Name and PName are both NULL in the data for that particular number. Just need to filter out any records where it finds those 3 Names that also have "Default" as the PName, then include everything else even if Name or Pname is NULL.
Below is my where clause.
WHERE [DETERMINATION] <> 'Denied'
AND [Number] ='A150731000039'
---- Removes incorrect records where these names match----
AND ([Name] NOT IN ('GLASSMAN','NANCY','LUDEMANN') AND [PName] = 'DEFAULT')
When i debug a trigger is it possible to add a WATCHon the INSERTED or DELETED?I think not, at least I couldn't figure out a way to do so.Does someone have a suggestion on how I can see the values?I did try to do something likeINSERT INTO TABLE1(NAME)SELECT NAME FROM INSERTEDbut this didn't work. When the trigger completed and Iwent to see the TABLE1, there were no records in it.Are there any documents, web links that describe waysof debugging the trigger's INSERTED and DELETED?Thank you
View 11 Replies View RelatedI have a robust query that returns a dataset and the data is good, however some of the records contain the exact same data with the exception of the 'Price' field. I want to combine the records that are identical and SUM the values in the 'Price' field. My query and example return dataset is below.
Query:
--------
select distinct
'On-Demand' as 'Business Line',
o.OrderID as 'Order #',
isnull(d.DisplayCode,'UNK') as Hub,
isnull(rz.RouteID,'UNK') as 'Default Route',
'On-Demand' as 'Assigned Route',
[Code] ....
I have a table with 1 million records. I want to update only 400 records. The update statement is provided by a 3rd party vendor. Once i run the update statement it will update all the 400 records. Once the table is updated the users will validate the table
if the update is successful or not. What i'm looking for is:
1) Is there a way to identify what records were updated.
2) If the update done is not what the users wanted i need to undo and bring back the 400 records to their previous values.
I'm on sql server 2008.
Hello,I am selecting some articles and some comments related with it:SELECT a.ArticleID, a.Title, a.Content, c.CommentId, c.Title, c.Comment, u.UserName AS ArticleAuthorName, u.UserEmail AS ArticleAuthorEmailFROM Articles aINNER JOIN Users u ON a.AuthorID = u.UserIDINNER JOIN Comments c ON a.ArticleID = c.ArticleIDI have 2 problems which I am trying to solve:1. Comments table also have an AuthorId So for each comment I also want to join to Users table and get the author name and email. How can I do this?2. I want to select all Articles even if it has comments or not. Can I use Inner Join or should I use Left Join? Is Outer Join still available in SQL 2005?Thank You,Miguel
View 2 Replies View Related Hi there,
I'm new to SQL.
I have encoutered a problem, I know how to select top 10 records from the database, but what about 11-20?
I can't use
Quote:
I have a simple table something like this
ID First Last
1 Bob Hope2 Charles Draw3 Mark Andrews
Etc, etc
I need the SQL statement select the last 10 records in the table, so if the highest ID id 82 I need all fields of records with ID 72-82
Help appreciated, i'm sure it must be simple enough buy mind is blank.
thanks
Hi there,
I'm new to SQL.
I have encoutered a problem, I know how to select top 10 records from the database, but what about 11-20?
I can't use
Quote: SELECT TOP 10 * FROM table WHERE id > 10
because even though my ID is auto numbered, but I have deleted some entries.
Does anyone have good solutions?
p.s I tried this and doesn't work either
Quote: SELECT TOP 10 * FROM table WHERE id IN(SELECT TOP 20 * FROM table ORDER by date DESC) ORDER BY date ASCENDING
TIA
-noPcz
Stijn writes "Hello,
I have SQL 7 with WINDOWS 2000 SERVER
My problem is that i am looking for a script
my db is for equipement inventory
how can i select all the records from a room,
if this room (room1)has less of 100 records he needs te go to the follow room(room2), en the next room until he's got 100 records,
sorry for my english"
Mack writes "I want to select last 2 records from a table AA which has following 5 records.
111
222
333
444
555
There is no primary key.
Any tip will be really appretiated.
Thanks for taking time to read it."